{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 传统数据库\n",
    "\n",
    "上篇文章：聊聊数据库~开篇 <https://www.cnblogs.com/dotnetcrazy/p/9690466.html>\n",
    "\n",
    "本来准备直接开讲NoSQL的（当时开篇就是说的NoSQL）考虑到有些同志可能连MySQL系都没接触过，所以我们2019说数据系的时候预计从`MySQL`（穿插`MSSQL`）开始，这篇文章就当试水篇，效果好就继续往下写~（这篇偏理论和运维）\n",
    "\n",
    "## 1.1.MariaDB and MySQL\n",
    "\n",
    "官方文档：`https://mariadb.com/kb/zh-cn/mariadb`\n",
    "\n",
    "目前主流：`MySQL 5.7.x` or **`MariaDB 5.5.60`**（推荐）\n",
    "\n",
    "多一句嘴，`MySQL`当年被`Oracle`收购后，`MySQL之父`觉得靠`Oracle`维护`MySQL`很不靠谱，然后就跳槽弄了个`MariaDB`（很多`Oracle`竞争对手扶持着），目前`MariaDB`是发展最快的`MySQL`分支版本（PS：`MySQL`现在是双协议了，大部分公司用的版本都是`<=5.7`）\n",
    "\n",
    "然后得说下迁移问题：`MySQL 5.x`到 `MariaDB 5.x`基本上是无缝的，MariaDB**最新稳定版**为：`MariaDB 5.5`\n",
    "> PS：**MariaDB有两个分支，而10.x分支是不兼容MySQL的**\n",
    "\n",
    "`MariaDB`与`MySQL`兼容性可以查看：\n",
    "> <https://mariadb.com/kb/zh-cn/mariadb-vs-mysql-compatibility/>\n",
    "\n",
    "PS：国内比较火的还有阿里的`MySQL分支`：`https://github.com/alibaba/AliSQL`\n",
    "\n",
    "不谈其他的，咱们看看它们开发的积极程度就知道为什么`MariaDB`是主流了\n",
    "![1.积极.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181226114927826-720399690.png)\n",
    "\n",
    "### 使用概括（推荐）\n",
    "\n",
    "如果想要使用`MariaDB10.x`的同志可以考虑`MySQL8.x`（社区完善）\n",
    "\n",
    "如果想要使用`MySQL5.x`的同志可以考虑`MariaDB5.5.x`（高性能且兼容）\n",
    "\n",
    "## 1.2.MariaDB部署\n",
    "\n",
    "网络配置如果不会可以看我以前写的文章：<https://www.cnblogs.com/dunitian/p/6658578.html>\n",
    "\n",
    "### 1.环境配置和初始化\n",
    "\n",
    "安装很简单，以`CentOS`为例：\n",
    "![1.CentOS.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181226231314430-1618955434.png)\n",
    "\n",
    "```shell\n",
    "systemctl start mariadb.service   # 启动MariaDB\n",
    "systemctl enable mariadb.service  # 设置开机启动\n",
    "\n",
    "systemctl stop mariadb.service    # 停止MariaDB\n",
    "systemctl restart mariadb.service # 重启MariaDB\n",
    "```\n",
    "\n",
    "![1.运行.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181227161744210-2130831622.png)\n",
    "\n",
    "PS：Win安装注意这一步：\n",
    "![1.win.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181226231342520-1201124099.png)\n",
    "\n",
    "执行文件简单说明：有时候我们 `ps aux | grep mysql` 的时候，发现运行的并不是`/usr/bin/`下的`mysqld`而是`mysqld_safe`，那这个`mysqld_safe`是啥呢？==> **线程安全的实例**\n",
    "\n",
    "`MariaDB`的程序组成：`ls /usr/bin | grep mysql`\n",
    "1. Client：\n",
    "   - **`mysql`**        命令行客户端\n",
    "   - **`mysqldump`**    数据库备份用\n",
    "   - **`mysqladmin`**   远程管理工具\n",
    "   - **`mysqlbinlog`**  二进制日志管理工具\n",
    "   - ...\n",
    "2. Server：\n",
    "   - **`mysqld_safe`**  线程安全的实例\n",
    "   - `mysqld_multi` 多实例\n",
    "   - `mysqld`\n",
    "   - **`mysql_secure_installation`** 安全初始化工具(记得先启动数据库哦)\n",
    "   - ...\n",
    "\n",
    "**`mysql`的账号由两部分组成：`username`@`host`，MySQL客户端连接参数：**\n",
    "- `-u用户名`：`--user`，默认为`root`\n",
    "- `-h服务器主机`：`--host`，默认为`localhost`\n",
    "    - `host`用于限制用户可以通过哪些主机连接\n",
    "    - 支持通配符：\n",
    "        - `%`匹配任意长度的任意字符：172.16.0.0/16 ==> 172.16.%.%\n",
    "        - `_`匹配任意单个字符\n",
    "- `-p密码`：`--password`，默认为`空`\n",
    "    - 安装完成后运行`mysql_secure_installation`来设置密码并初始化\n",
    "- other：\n",
    "    - `-P`：`--port`，指定端口\n",
    "    - `-D`：`--database`，指定数据库\n",
    "    - `-C`：`--compress`，连接数据库的时候对传输的数据压缩\n",
    "    - `-S`：`--socket`，指定socket文件\n",
    "- MySQL专用：-e \"SQL语句\"，直接执行SQL语句\n",
    "    - mysql -e \"show databases\"（脚本直接运行）\n",
    "\n",
    "很多人安装完成后是这样设置密码的：(**不推荐**)\n",
    "![1.不推荐.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181226230835330-601019989.png)\n",
    "\n",
    "**正确打开方式：`mysql_secure_installation`**\n",
    "![1.安全初始化1.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181226225313373-1042152945.png)\n",
    "\n",
    "如果允许root远程登录：`Disallow root login remotely? [Y/n] n`\n",
    "![1.安全初始化2.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181226225701877-196565629.png)\n",
    "\n",
    "安全初始化后登录图示：\n",
    "![1.安全初始化后登录.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181226230645032-776720431.png)\n",
    "![1.基本信息.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181226232704832-766679019.png)\n",
    "\n",
    "### 2.配置文件\n",
    "\n",
    "以`MariaDB 5.5.60`为例：\n",
    "1. Linux：配置文件查找顺序(找不到就往下继续)\n",
    "   - `/etc/my.cnf` --> **`/etc/mysql/conf.d/*.cnf`** --> `~/.my.cnf`\n",
    "2. Windows：`MariaDB安装目录/data/my.ini`\n",
    "\n",
    "PS：一般配置文件都会设置这3个\n",
    "```shell\n",
    "[mysqld]\n",
    "# 独立表空间: 每一个表都有一个.frm表描述文件，还有一个.ibd文件\n",
    "innodb_file_per_table=on\n",
    "# 不对连接进行DNS解析(省时)\n",
    "skip_name_resolve=on\n",
    "# 配置sql_mode\n",
    "sql_mode='strict_trans_tables'\n",
    "\n",
    "# 指定数据库文件存放路径\n",
    "# datadir=/mysql/data\n",
    "# socket=/mysql/data/mysql.sock # 与之对应\n",
    "```\n",
    "其他配置`MariaDB`提供了样本：\n",
    "```shell\n",
    "[dnt@localhost ~] ls /usr/share/mysql/ | grep .cnf\n",
    "my-huge.cnf            # 超大内存配置参考\n",
    "my-innodb-heavy-4G.cnf # 4G内存配置参考\n",
    "my-large.cnf           # 大内存配置\n",
    "my-medium.cnf          # 中等内存配置\n",
    "my-small.cnf           # 小内存配置\n",
    "```\n",
    "PS：`thread_concurrency`=`CPU数*2`最佳，**修改配置后记得重启数据库**\n",
    "\n",
    "### 3.远程访问\n",
    "\n",
    "1.之前安全初始化的时候把`root`禁止远程登录了，现在我们创建一个其他用户\n",
    "![1.新增用户.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181227153211084-1542804218.png)\n",
    "\n",
    "2.给用户权限\n",
    "![1.给权限.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181227154329334-308689111.png)\n",
    "\n",
    "3.防火墙放行指定端口\n",
    "![1.防火墙.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181227154746690-1949205505.png)\n",
    "\n",
    "4.远程客户端测试一下\n",
    "![1.成功.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181227132048960-1914298109.png)\n",
    "\n",
    "Code如下：\n",
    "```shell\n",
    "# root账户登录\n",
    "mysql -uroot -p\n",
    "\n",
    "# 新增用户\n",
    "insert into mysql.user(user,host,password) values(\"用户名\",\"%\",password(\"密码\"));\n",
    "\n",
    "# 刷新设置\n",
    "flush privileges;\n",
    "\n",
    "# 分配权限\n",
    "grant all privileges on 数据库.* to 用户名@\"%\" identified by \"密码\";\n",
    "\n",
    "# 刷新设置\n",
    "flush privileges;\n",
    "\n",
    "# 显示服务状态\n",
    "systemctl status firewalld\n",
    "\n",
    "# 添加 --permanent永久生效(没有此参数重启后失效)\n",
    "firewall-cmd --zone=public --add-port=3306/tcp --permanent\n",
    "\n",
    "# 重新载入\n",
    "firewall-cmd --reload\n",
    "\n",
    "# 查看\n",
    "firewall-cmd --zone= public --query-port=3306/tcp\n",
    "# 删除\n",
    "firewall-cmd --zone= public --remove-port=3306/tcp --permanent\n",
    "```\n",
    "\n",
    "**SQLServer远程连接**：<https://www.cnblogs.com/dunitian/p/5474501.html>\n",
    "\n",
    "## MySQL军规（58）\n",
    "\n",
    "文章结尾贴一节`58`的`MySQL`军规：（**适用于并发量大，数据量大的典型互联网业务**）\n",
    "\n",
    "### 1.基础规范\n",
    "\n",
    "1. 表存储引擎必须使用`InnoDB`\n",
    "2. 表字符集默认使用`utf8`，必要时候使用`utf8mb4`\n",
    "    - `utf8`通用，无乱码风险，汉字3字节，英文1字节\n",
    "    - `utf8mb4`是`utf8的超集`，存储4字节时使用（eg：表情符号）\n",
    "3. **禁止使用存储过程，视图，触发器，Event**\n",
    "    - 调试，排错，迁移都比较困难，扩展性较差\n",
    "    - 对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，不要交到数据库层\n",
    "4. 禁止在数据库中存储大文件（eg：照片）\n",
    "    - 可以将大文件存储在对象存储系统，数据库中存储路径\n",
    "5. 禁止在线上环境做数据库压力测试\n",
    "    - 测试，开发，线上数据库环境必须隔离\n",
    " \n",
    "### 2.命名规范\n",
    "\n",
    "1. **库名，表名，列名必须用小写，采用下划线分隔**\n",
    "    - abc，Abc，ABC都是给自己埋坑\n",
    "2. 库名，表名，列名必须见名知义，长度不要超过32字符\n",
    "    - tmp，wushan谁TM知道这些库是干嘛的\n",
    "3. 库备份必须以bak为前缀，以日期为后缀\n",
    "    - 从库必须以-s为后缀\n",
    "    - 备库必须以-ss为后缀\n",
    " \n",
    "### 3.表设计规范\n",
    "\n",
    "1. 单实例表个数必须控制在`2000`个以内\n",
    "2. 单表分表个数必须控制在`1024`个以内\n",
    "3. **表必须有主键，推荐使用`unsigned`整数为主键**\n",
    "    - 潜在坑：删除无主键的表，如果是row模式的主从架构，从库会挂住\n",
    "4. 禁止使用外键，如果要保证完整性，应由应用程式实现\n",
    "    - 外键使得表之间相互耦合，影响`update/delete`等SQL性能\n",
    "    - 有可能造成死锁，高并发情况下容易成为数据库瓶颈\n",
    "5. 建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据\n",
    "    - 垂直拆分的依据，尽量把长度较短，访问频率较高的属性放在主表里\n",
    "    - 流量大数据量大时，数据访问要有`service`层，并且`service`层不要通过`join`来获取主表和扩展表的属性\n",
    "    - 具体可以参考沈剑大牛写的<a href=\"https://mp.weixin.qq.com/s/ezD0CWHAr0RteC9yrwqyZA\" target=\"_blank\">《如何实施数据库垂直拆分》</a>\n",
    " \n",
    "### 4.列设计规范\n",
    "\n",
    "1. 根据业务区分使用`tinyint`/`int`/`bigint`，分别会占用`1`/`4`/`8`字节\n",
    "2. 根据业务区分使用`char`/`varchar`（PS：没有MSSQL里的`nvarchar`）\n",
    "    - 字段长度固定，或者长度近似的业务场景，适合使用`char`，**能够减少碎片，查询性能高**\n",
    "    - 字段长度相差较大，或者更新较少的业务场景，适合使用`varchar`，能够**减少空间**\n",
    "3. 根据业务区分使用`datetime`/`timestamp`\n",
    "    - `datetime`占用5个字节，`timestamp`占用4个字节\n",
    "    - 存储年使用`year`，存储日期使用`date`，存储时间使用`datetime`\n",
    "4. **必须把字段定义为`NOT NULL`并设默认值**\n",
    "    - NULL需要更多的存储空间\n",
    "    - NULL的列使用索引，索引统计，值都更加复杂，MySQL更难优化\n",
    "    - NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑\n",
    "5. **使用`int unsigned`存储`IPv4`**，不要用`char(15)`\n",
    "6. **使用`varchar(20)`存储手机号，不要使用整数**\n",
    "    - 手机号不会用来做数学运算\n",
    "    - `varchar`可以模糊查询(eg：like ‘138%’)\n",
    "    - 牵扯到国家代号，可能出现`+、-、()`等字符，eg：`+86`\n",
    "7. 使用`tinyint`来代替`enum`\n",
    "    - `enum`增加新值要进行`DDL`操作\n",
    "\n",
    "### 5.索引规范（常用）\n",
    "\n",
    "1. 唯一索引使用`uniq_字段名`来命名（`uq_表名_字段名`）\n",
    "2. 非唯一索引使用`idx_字段名`来命名（`ix_表名_字段名`）\n",
    "3. **单张表索引数量建议控制在5个以内**\n",
    "    - 互联网高并发业务，太多索引会影响写性能\n",
    "    - 异常复杂的查询需求，可以选择`ES`等更为适合的方式存储\n",
    "    - `生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL选择不到最优索引`\n",
    "4. **组合索引字段数不建议超过5个**\n",
    "    - 如果5个字段还不能极大缩小row范围，八成是设计有问题\n",
    "5. **不建议在频繁更新的字段上建立索引**\n",
    "6. **尽量不要`join`查询，如果要进行`join`查询，被`join`的字段必须类型相同，并建立索引**\n",
    "    - `join`字段类型不一致容易导致全表扫描\n",
    "7. 理解组合索引最左前缀原则，避免重复建设索引\n",
    "    - 如果建立了`(a,b,c)`，相当于建立了`(a)`, `(a,b)`, `(a,b,c)`\n",
    "\n",
    "### 6.SQL规范（常用）\n",
    "\n",
    "1. **禁止使用`select *`，只获取必要字段**\n",
    "    - 指定字段能有效利用索引覆盖\n",
    "    - `select *`会增加`cpu/io/内存/带宽`的消耗\n",
    "    - `指定字段查询，在表结构变更时，能保证对应用程序无影响`\n",
    "2. **`insert`必须指定字段，禁止使用`insert into T values()`**\n",
    "    - 指定字段插入，在表结构变更时，能保证对应用程序无影响\n",
    "3. **隐式类型转换会使索引失效，导致全表扫描**（很重要）\n",
    "4. 禁止在`where`条件列使用函数或者表达式\n",
    "    - 导致不能命中索引，全表扫描\n",
    "5. 禁止负向查询以及`%`开头的模糊查询\n",
    "    - 导致不能命中索引，全表扫描\n",
    "6. 禁止大表`join`和`子查询`\n",
    "7. **同一个字段上的`or`必须改写为`in`，`in`的值必须少于50个**\n",
    "8. 应用程序必须捕获SQL异常（方便定位线上问题）\n",
    "\n",
    "课后思考：为什么`select uid from user where phone=13811223344`不能命中phone索引？\n",
    "\n",
    "课后拓展：\n",
    "```\n",
    "MyISAM与InnoDB两者之间区别与选择\n",
    "https://www.cnblogs.com/y-rong/p/5309392.html\n",
    "https://www.cnblogs.com/y-rong/p/8110596.html\n",
    "\n",
    "了解下Mysql的间隙锁及产生的原因\n",
    "https://www.cnblogs.com/wt645631686/p/8324671.html\n",
    "\n",
    "grant授权和revoke回收权限\n",
    "https://www.cnblogs.com/kevingrace/p/5719536.html\n",
    "\n",
    "centos7自带数据库MariaDB重启和修改密码\n",
    "https://blog.csdn.net/shachao888/article/details/50341857\n",
    "\n",
    "MySQL添加用户、删除用户与授权\n",
    "https://www.cnblogs.com/wanghetao/p/3806888.html\n",
    "\n",
    "深度认识 Sharding-JDBC：做最轻量级的数据库中间层\n",
    "https://my.oschina.net/editorial-story/blog/888650\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "上篇回顾：<a href=\"https://www.cnblogs.com/dotnetcrazy/p/9887708.html\" target=\"_blank\">聊聊数据库~SQL环境篇</a>\n",
    "\n",
    "### 扩展：为用户添加新数据库的权限\n",
    "\n",
    "PS：先使用root创建数据库，然后再授权`grant all privileges on 数据库.* to 用户名@\"%\" identified by \"密码\";`并刷新`flush privileges;`\n",
    "\n",
    "![3.1.为用户添加新数据库权限.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190213113305619-8353123.png)\n",
    "\n",
    "查看权限：`show grants for dnt;`\n",
    "![3.2.查看权限.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190213121030899-1828853833.png)\n",
    "\n",
    "效果：\n",
    "![3.3.效果.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190213113701068-681620404.png)\n",
    "\n",
    "## 1.3.MySQL部署\n",
    "\n",
    "之前有园友说，为啥不顺便说说`UbuntuServer`的部署呢？呃。。。一般来说公司服务器都是`CentOS`的占大多数，然后`UbuntuServer`更多的是个人云服务比较多（**推荐初创公司使用**），毕竟它们两个系统追求的不太一样，一个是追求稳（部署麻烦），一个是追求软件尽量新的情况下稳定（更新太快）\n",
    "\n",
    "那么长话短说，步入正轨：\n",
    "\n",
    "### 1.Ubuntu最常见的包问题\n",
    "\n",
    "Ubuntu不得不说的就是这个**`apt`出问题的处理** ：（换源就不说了`/etc/apt/sources.list`）\n",
    "```shell\n",
    "# 一般删除这几个锁文件，然后再重新配置下就可以了\n",
    "sudo rm /var/lib/dpkg/lock\n",
    "sudo rm /var/lib/dpkg/lock-frontend\n",
    "sudo rm /var/lib/apt/lists/lock\n",
    "sudo rm /var/cache/apt/archives/lock\n",
    "# 简写（千万注意空格，不然你就是rm -rf / + 跑路了）\n",
    "# sudo rm /var/lib/apt/lists/lock /var/cache/apt/archives/lock /var/lib/dpkg/lock /var/lib/dpkg/lock-frontend\n",
    "\n",
    "# 重新配置下\n",
    "sudo dpkg --configure -a\n",
    "```\n",
    "\n",
    "### 2.安装注意（Ubuntu的特点就是使用起来简单）\n",
    "\n",
    "`Ubuntu`推荐使用`MySQL`（毕竟同是`5.x`用起来基本上差不多，安装过程和之前说的`CentOS 下 MariaDB`差不多，所有命令前加个**`sudo`**）\n",
    "\n",
    "1.安装比较简单：`sudo apt install mysql-server -y`\n",
    "![2.1.Ubuntu.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181230101637706-882392574.png)\n",
    "\n",
    "2.允许远程连接：`注释掉 bind-address=127.0.0.1`(`/etc/mysql/mysql.conf.d/mysqld.cnf`)\n",
    "![2.2.远程连接.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181230101658414-1379371907.png)\n",
    "\n",
    "PS：常用配置（`/etc/mysql/mysql.conf.d/mysqld.cnf`）\n",
    "![2.2.1.常用配置.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190213130922734-988035463.png)\n",
    "\n",
    "3.关于为什么是这个路径的说明：`sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf`\n",
    "![2.3.为什么是这个路径.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181230101715440-931363578.png)\n",
    "\n",
    "4.所有配置修改都需要重新启动下：`sudo systemctl restart mysql`\n",
    "![2.4.记得重启一下](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181230101808123-1679185302.png)\n",
    "\n",
    "5.第一次初始化和MariaDB不太一样：`sudo mysql_secure_installation`（其他一路`y`即可）\n",
    "\n",
    "需要选下你设置root密码的复杂度：（一般1就可以了，就算设置再复杂，入了系统也是虚的）\n",
    "![2.5.初始化-注意下密码复杂程度](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181230101832777-1084161641.png)\n",
    "\n",
    "PS：可以看看拓展文章：<a href=\"https://www.cnblogs.com/super-zhangkun/p/9435974.html\" target=\"_blank\">Ubuntu16安装mysql5.7未提示输入密码，安装后修改mysql默认密码</a> 和 <a href=\"https://blog.csdn.net/hello_world_qwp/article/details/79551789\" target=\"_blank\">【不推荐】修改mysql密码策略</a>\n",
    "\n",
    "6.然后输入密码你就可以登录了`sudo mysql -uroot -p`（PS：你直接`sudo mysql`也可以直接登录）\n",
    "\n",
    "这边我就不像上节课一步步演示了，直接授权和创建一步走了`grant all privileges on 数据库.* to \"用户名\"@\"%\" identified by \"复杂密码\";`\n",
    "![2.6.授权创建一步走.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181230105705748-187812189.png)\n",
    "\n",
    "7.记得`flush privileges;`刷新一下系统表\n",
    "![2.7.测试.png](https://img2018.cnblogs.com/blog/1127869/201812/1127869-20181230110429390-1200971137.png)\n",
    "\n",
    "PS：数据文件一般都是放在`/var/lib/mysql`中\n",
    "\n",
    "#### 课后拓展：\n",
    "\n",
    "MySQL5.7.26 忘记Root密码小计：<https://www.cnblogs.com/dotnetcrazy/p/11027732.html>\n",
    "\n",
    "```\n",
    "浅析MySQL 8忘记密码处理方式\n",
    "https://www.cnblogs.com/wangjiming/p/10363357.html\n",
    "\n",
    "MySQL5.6更改datadir数据存储目录\n",
    "https://www.cnblogs.com/ding2016/p/7644675.html\n",
    "```\n",
    "\n",
    "#### 扩展：CentOS7安装MySQL8\n",
    "\n",
    "<a href=\"https://www.cnblogs.com/dotnetcrazy/p/10871352.html\">CentOS7安装MySQL8.0安装小计</a>：<https://mp.weixin.qq.com/s/Su3Ivuy5IMeAwYBXaka0ag>\n",
    "\n",
    "---\n",
    "\n",
    "## 1.4.基础（MySQL and SQLServer）\n",
    "\n",
    "脚本示例：<https://github.com/lotapp/BaseCode/tree/master/database/SQL> \n",
    "\n",
    "**PS：在MySQL中运行SQL脚本：`mysql < script.sql`**\n",
    "\n",
    "后面代码优先使用通用SQL（`MySQL`和`SQLServer`（`MSSQL`）通用的SQL语句），逆天好几年没用`SQLServer`了（几年前讲过MSSQL），这边就一带而过（欢迎纠错）\n",
    "\n",
    "**PS：后面`MariaDB`我也直接说成`MySQL`了（有区别的地方我会说下，毕竟也是MySQL的分支，相似度还是很大的）**\n",
    "\n",
    "### 1.概念\n",
    "\n",
    "#### 1.1.传统概念\n",
    "\n",
    "来说说传统概念：\n",
    "1. 关系型数据库中的**关系**：表（行、列）\n",
    "2. **设计范式**：\n",
    "    - 第1范式：字段是原子性的\n",
    "    - 第2范式：每个表需要一个主键\n",
    "    - 第3范式：任何表都不应该有依赖于其他**非主键**表的字段\n",
    "\n",
    "- **DDL**：数据定义语言(Data Defination Language)\n",
    "  - `create、drop、alter`\n",
    "- **DML**：数据操作语言(Data Manipulation Language)\n",
    "  - **`insert、delete、update、select`**\n",
    "- **DCL**：数据库控制语言（Data Control Language）\n",
    "  - `grant`(授权)、`revoke`(回收)\n",
    "\n",
    "**PS：`CURD`（定义了用于处理数据的基本原子操作）：创建（Create）更新（Update）读取（Retrieve）删除（Delete）操作**\n",
    "\n",
    "#### 1.2.常见组件\n",
    "\n",
    "关系型数据库常见组件：\n",
    "1. **数据库**：database\n",
    "2. **表**：table\n",
    "    - 行：row\n",
    "    - 列：column\n",
    "3. **索引**：index\n",
    "4. **视图**：view\n",
    "    - PS：如果有`数据库迁移`的需求则不建议使用\n",
    "    - PS：MySQL的视图功能不是特别完素，尽量不使用\n",
    "5. 存储过程：procedure\n",
    "6. 存储函数：function\n",
    "7. 触发器：trigger\n",
    "8. 事件调度器：event、scheduler\n",
    "9. 用户：user\n",
    "10. 权限：privilege\n",
    "\n",
    "PS：MySQL常见的文件类型：\n",
    "1. 数据：数据文件、索引文件\n",
    "2. 日记：错误日志、查询日志、慢查询日志、二进制日志、（重做日志、撤销日志、中继日志）\n",
    "\n",
    "### 2.MySQL标准存储引擎\n",
    "\n",
    "#### 2.1.MySQL\n",
    "\n",
    "先说说`MySQL`标准存储引擎(**`表类型`**)：\n",
    "1. **`MyISAM`**：只支持`表级锁`，不支持`事务`\n",
    "2. **`InnoDB`**：支持`事务`、`间隙锁`、`行锁`等等\n",
    "\n",
    "#### 2.2.MariaDB\n",
    "\n",
    "首先是**插件式存储引擎(`表类型`)的改进和扩充** PS：其实也就是支持更多的存储引擎（包括自定义的）\n",
    "\n",
    "`MariaDB`对标准存储引擎进行了改造升级：\n",
    "1. `MyISAM` ==> `Aria`：支持崩溃后的恢复\n",
    "2. `InnoDB` ==> **`XtraDB`**：优化存储性能\n",
    "\n",
    "还进行了很多扩展并开发了新的功能（也提供了很多测试工具），比如添加一些`NoSQL`的功能（`SQLServer`也扩展了`NoSQL`）\n",
    "\n",
    "### 3.创建、删除（数据库 | 表）\n",
    "\n",
    "#### 字段类型（含异同）\n",
    "\n",
    "官方文档：\n",
    "- `https://mariadb.com/kb/en/library/data-types`\n",
    "- `https://dev.mysql.com/doc/refman/5.7/en/data-types.html`\n",
    "\n",
    "以`MariaDB`为例，简单列举下常用类型：(倾体说明和`MySQL`不一样)\n",
    "1. 字符型：\n",
    "    1. 定长字符型：\n",
    "        - **`char()`**：不区分字符大小写类型的字符串,`max：255个字符`\n",
    "        - binary()：区分字符大小写类型的二进制字符串\n",
    "    2. 变长字符型：\n",
    "        - **`varchar()`**：  不区分字符大小写类型的字符串\n",
    "            - max：65535（2^16 - 1）个字节（`utf8编码下最多支持21843个字符`）\n",
    "            - 可以理解为`SQLServer`的`nvarchar`\n",
    "        - varbinary()：区分字符的大小写类型的二进制字符串\n",
    "    3. 对象存储：\n",
    "        - **`text`**：不区分字符大小写的不限长字符串\n",
    "            - 最大长度为65,535（2^16 - 1）个字符\n",
    "            - 如果值包含多字节字符，则有效最大长度减少\n",
    "        - blob：区分字符大小写的不限长二进制字符串\n",
    "    4. 内建类型：（不推荐使用）\n",
    "        - enum：单选字符串数据类型，适合表单中的`单选值`\n",
    "        - set：多选字符串数据类型，适合表单的`多选值`\n",
    "        - **PS：`MySQL系`独有，`SQLServer`没有**\n",
    "2. 数值型：\n",
    "    1. 精确数值型：\n",
    "        - 整型：int\n",
    "            1. _bool：布尔类型_（MySQL没有）\n",
    "                - **PS：`SQLServer`是`bit`**\n",
    "                - **相当于`MySQL`的`tinyint(1)`**\n",
    "            2. **`tinyint`**：微小整型（1字节，8位）\n",
    "                - `[-2^7, 2^7)`(`-128 ~ 127`)\n",
    "                - 无符号：`[0, 2^8)`(`0 ~ 255`)\n",
    "            3. smallint（2bytes，16bit）：小整型\n",
    "                - 无符号：`0 ~ 65535`\n",
    "            4. mediumint（3bytes,24位）：中等整型\n",
    "                - `PS：SQLServer中没这个类型`\n",
    "            5. **`int`**（4bytes，32bit）\n",
    "                - `[-2^31, 2^31)`，`[-2147483648,2147483648)`\n",
    "                - 无符号：`[0, 2^32)`，`[0,4294967296)`\n",
    "            6. **`bigint`**（8bytes，64bit）\n",
    "                - `[-2^63, 2^63)`\n",
    "                - 无符号：`[0, 2^64)`\n",
    "    2. 浮点类型：\n",
    "        - float：单精度浮点数（4字节）\n",
    "        - **`double`**：双精度浮点数（8字节）\n",
    "            - **PS：`SQLServer`的`float`类型相当于`MySQL`的`double`**\n",
    "        - **`decimal`**：精确小数类型（比double更精确）\n",
    "            - 钱相关用的比较多：`decimal(位数,小数点位数)`\n",
    "            - eg：`decimal(2,1)` => `x.x`\n",
    "3. **日期和时间类型**：（和`MySQL`一样）\n",
    "    1. date：日期（3bytes）\n",
    "    2. time：时间（3bytes）\n",
    "    3. year：年\n",
    "        - eg：`year(2)`：`00~99（1bytes）`\n",
    "        - eg：`year(4)`：`0000~9999（1bytes）`\n",
    "        - **PS：`SQLServer没有这个类型`**\n",
    "    4. **`datetime`**：既有时间又有日期（8bytes）\n",
    "    5. **`timestamp`**：时间戳（4bytes）【精度更高】\n",
    "4. 修饰符：\n",
    "    - 所有类型都适用：\n",
    "        - 是否为null：`null` | `not null`\n",
    "        - 默认值：`default xxx_value`\n",
    "        - 主  键：`primary key`\n",
    "        - 唯一键：`unique key`\n",
    "    - 数值类型适用： \n",
    "        - **无符号：`unsigned`**（MySQL系独有）\n",
    "        - 自增长：**`auto_increment`** （一般只用于整型，MSSQL是`identity`）\n",
    "            - 获取ID：`last_insert_id()`\n",
    "    - PS：**多列设置**：\n",
    "        1. 主键：`primary key(xx,...)`\n",
    "        2. 唯一键：`unique key(xx,...)`\n",
    "        3. 索引：`index index_name (xx,...)`\n",
    "\n",
    "PS：现在新版本数据库兼容了SQLServer的`nvarchar`写法（`执行成功后数据类型变成varchar`）【不推荐使用】\n",
    "\n",
    "课后拓展：\n",
    "```\n",
    "MySQL：char、varchar、text的区别\n",
    "https://dev.mysql.com/doc/refman/5.7/en/char.html\n",
    "https://blog.csdn.net/brycegao321/article/details/78038272\n",
    "```\n",
    "\n",
    "#### 3.1.MySQL\n",
    "\n",
    "知识点概括：\n",
    "1. 创建数据库：\n",
    "    - `create database [if not exists] db_name;`\n",
    "2. 删除数据库：\n",
    "    - `drop database [if exists] db_name;`\n",
    "3. 创建表：\n",
    "    - `create table [if not exists] tb_name(列名1,数据类型 修饰符,列名2,数据类型 修饰符);`\n",
    "4. 删除表：\n",
    "    - `drop table [if exists] db_name.tb_name;`\n",
    "5. 修改表：\n",
    "    1. 字段\n",
    "        - 添加字段：add\n",
    "            - `alter table tb_name add 列名 数据类型 修饰符 [first | after 列名];`\n",
    "            - **PS：SQLServer没有`[first | after 列名]`**\n",
    "        - 修改字段：alter、change、modify\n",
    "            - 修改字段名：`alter table tb_name change 旧列名 新列名 类型 类型修饰符`\n",
    "            - 修改字段类型：`alter table tb_name modify 列名 类型 类型修饰符`\n",
    "            - 添加默认值：`alter table tb_name alter 列名 set default df_value`\n",
    "        - 删除字段：drop\n",
    "            - `alter table tb_name drop 字段名`\n",
    "    2. 索引\n",
    "        - 添加索引：add（常用：**`create index index_name on tb_name(列名,...）;`**）\n",
    "            - `alter table tb_name add index [ix_name] (列名,...);`\n",
    "            - 添加唯一键：`alter table tb_name add unique [uq_name] (列名,列名2...);`\n",
    "            - **PS：不指定索引名字，默认就是第一个字段名**\n",
    "        - 删除索引：drop（常用：**`drop index index_name on tb_name`**）\n",
    "            - `alter table tb_name drop index index_name;`\n",
    "            - 删除唯一键：`alter table tb_name drop index uq_name;`\n",
    "            - **PS：唯一键的索引名就是第一个列名**\n",
    "        - **PS：一般在经常用做查询条件的列设置索引**\n",
    "    3. 表选项\n",
    "        - 可以参考这篇文章：`https://www.cnblogs.com/huangxm/p/5736807.html`\n",
    "6. **`SQL Model`**：定义MySQL对约束的响应行为：\n",
    "    - 会话修改：\n",
    "        - mysql> `set [session] sql_model='xx_mode'`\n",
    "        - mysql> `set @@session.sql_mode='xx_mode'`\n",
    "        - **PS：只在当前会话生效**\n",
    "    - 全局修改：需要有权限，并且不会立即生效，对以后新建的会话生效（从全局继承的）\n",
    "        - mysql> `set global sql_mode='xx_mode'`\n",
    "        - mysql> `set @@global.sql_mode='xx_mode'`\n",
    "        - **PS：MySQL重启后失效**\n",
    "    - 配置修改：永远生效：\n",
    "        - eg：`vi /etc/my.cnf`，在`[mysqld]`下添加`sql_mode='xx'`，然后重启数据库\n",
    "        - PS：从MySQL8开始,可通过`set persist`命令将全局变量的修改持久化到配置文件中\n",
    "            - **持久化到`/var/lib/mysql/mysqld-auto.cnf`配置文件中**\n",
    "            - eg：`set persist log_timestamps='SYSTEM';`（需要root权限）\n",
    "    - **常用mode**：（阿里服务器默认是：`strict_trans_tables`）\n",
    "        - **`traditional`**：使用传统模型，不允许对非法值做插入操作\n",
    "        - **`strict_trans_tables`**：对所有支持事物类型的表做严格约束\n",
    "        - `strict_all_tables`：对所有表做严格约束\n",
    "        - 查询当前设置：**`select @@sql_mode`**\n",
    "    - 详情可以查看我之前写的文章：<https://www.cnblogs.com/dotnetcrazy/p/10374091.html>\n",
    "\n",
    "##### 3.1.1.创建、删除数据库\n",
    "\n",
    "```sql\n",
    "-- 如果存在就删除数据库\n",
    "drop database if exists dotnetcrazy;\n",
    "\n",
    "-- 创建数据库\n",
    "create database if not exists dotnetcrazy;\n",
    "```\n",
    "\n",
    "##### 3.1.2.创建、删除表\n",
    "\n",
    "```sql\n",
    "-- 如果存在就删除表\n",
    "drop table if exists dotnetcrazy.users;\n",
    "\n",
    "-- mysql> help create table（低版本的默认值不支持函数）\n",
    "-- 创建表 create table users(字段名 类型 修饰符,...)\n",
    "create table if not exists dotnetcrazy.users\n",
    "(\n",
    "    id         int unsigned auto_increment,                       -- 主键，自增长【获取ID：last_insert_id()】\n",
    "    username   varchar(20) not null,\n",
    "    password   char(40)    not null,                              -- sha1：40\n",
    "    email      varchar(50) not null,\n",
    "    ucode      char(36)    not null,-- default uuid(),          -- uuid\n",
    "    createtime datetime    not null,-- default now(),\n",
    "    updatetime datetime    not null,-- default now(),\n",
    "    datastatus tinyint     not null default 0,                    -- 默认值为0\n",
    "    primary key (id),                                             -- 主键可多列\n",
    "    unique uq_users_email (email),\n",
    "    index ix_users_createtime_updatetime (createtime, updatetime) -- 索引，不指定名字默认就是字段名\n",
    ")\n",
    "--   表选项\n",
    "--   engine = 'innodb', -- 引擎\n",
    "--   character set utf8, -- 字符集\n",
    "--   collate utf8_general_ci, -- 排序规则\n",
    ";\n",
    "```\n",
    "\n",
    "##### 3.1.3.修改表\n",
    "\n",
    "```sql\n",
    "-- 修改表 mysql> help alter table\n",
    "\n",
    "-- 3.1.添加一列 alter table tb_name add 列名 数据类型 修饰符 [first | after 列名]\n",
    "alter table dotnetcrazy.users\n",
    "    add uid bigint not null unique first; -- MSSQL没有[first | after 列名]\n",
    "\n",
    "-- 在email后面添加手机号码列\n",
    "-- 手机号不会用来做数学运算，varchar可以模糊查询(eg：like ‘138%’)\n",
    "-- 牵扯到国家代号时，可能出现+、-、()等字符，eg：+86\n",
    "alter table dotnetcrazy.users\n",
    "    add tel varchar(20) not null after email;\n",
    "\n",
    "-- 3.2.删除一列 alter table tb_name drop 字段名\n",
    "alter table dotnetcrazy.users\n",
    "    drop uid;\n",
    "\n",
    "-- 3.3.添加索引 alter table tb_name add index [ix_name] (列名,...)\n",
    "alter table dotnetcrazy.users\n",
    "    add index ix_users_ucode (ucode); -- 不指定名字默认就是字段名\n",
    "-- add index (ucode, tel); -- 不指定索引名字，默认就是第一个字段名\n",
    "\n",
    "-- 添加唯一键 alter table tb_name add unique [uq_name] (列名,列名2...)\n",
    "alter table dotnetcrazy.users\n",
    "    add unique uq_users_tel_ucode (tel, ucode);\n",
    "-- add unique (tel, ucode);-- 不指定索引名字，默认就是第一个字段名\n",
    "\n",
    "-- 3.4.删除索引 alter table tb_name drop index ix_name\n",
    "alter table dotnetcrazy.users\n",
    "    drop index ix_users_ucode;\n",
    "\n",
    "-- 删除索引（唯一键) alter table tb_name drop index uq_name\n",
    "alter table dotnetcrazy.users\n",
    "    drop index uq_users_tel_ucode;\n",
    "-- drop index tel; -- 唯一键的索引名就是第一个列名\n",
    "\n",
    "-- 3.5.修改字段\n",
    "-- 1.修改字段名：`alter table tb_name change 旧列名 新列名 类型 类型修饰符`\n",
    "-- 此时一定要重新指定该列的类型和修饰符\n",
    "alter table dotnetcrazy.users\n",
    "    change ucode usercode char(36); -- default uuid();\n",
    "\n",
    "-- 2.修改字段类型\n",
    "alter table dotnetcrazy.users\n",
    "    modify username varchar(25) not null;\n",
    "\n",
    "-- 3.添加默认值：`alter table tb_name alter 列名 set default df_value`\n",
    "alter table dotnetcrazy.users\n",
    "    alter password set default '7c4a8d09ca3762af61e59520943dc26494f8941b';\n",
    "```\n",
    "\n",
    "#### 3.2.SQLServer\n",
    "\n",
    "示例服务器：`SQLServer 2014`\n",
    "\n",
    "##### 3.2.1.创建、删除数据库\n",
    "\n",
    "```sql\n",
    "use master\n",
    "\n",
    "--存在就删除\n",
    "if exists(select *\n",
    "          from sysdatabases\n",
    "          where Name = N'dotnetcrazy')\n",
    "  begin\n",
    "    drop database dotnetcrazy\n",
    "  end\n",
    "\n",
    "--创建数据库（简化版：create database dotnetcrazy）\n",
    "create database dotnetcrazy\n",
    "  on primary --数据库文件，主文件组\n",
    "  (\n",
    "    name ='dotnetcrazy_Data', --逻辑名\n",
    "    size =10 mb, --初始大小\n",
    "    filegrowth =10%, --文件增长\n",
    "    maxsize =1024 mb, --最大值\n",
    "    filename =N'D:\\Works\\SQL\\dotnetcrazy_data.mdf'--存放路径（包含文件后缀名）\n",
    "    )\n",
    "  log on --日记\n",
    "  (\n",
    "    name ='dotnetcrazy_Log',\n",
    "    size =5 mb,\n",
    "    filegrowth =5%,\n",
    "    filename =N'D:\\Works\\SQL\\dotnetcrazy_log.ldf'\n",
    "    );\n",
    "\n",
    "-- 切换数据库\n",
    "use dotnetcrazy;\n",
    "```\n",
    "\n",
    "##### 3.2.2.创建、删除表\n",
    "\n",
    "```sql\n",
    "--存在就删除表\n",
    "if exists(select *\n",
    "          from sysobjects\n",
    "          where name = N'users')\n",
    "  begin\n",
    "    drop table users\n",
    "  end\n",
    "\n",
    "-- dotnetcrazy.dbo.users\n",
    "create table users\n",
    "(\n",
    "  id         int identity,                                      -- 主键，自增长\n",
    "  username   nvarchar(20) not null,\n",
    "  email      varchar(50)  not null,\n",
    "  password   char(40)     not null,                             -- sha1\n",
    "  ucode      char(36)     not null default newid(),             -- guid\n",
    "  createtime datetime     not null default getdate(),\n",
    "  updatetime datetime     not null default getdate(),\n",
    "  datastatus tinyint      not null default 0,                   -- 默认值为0\n",
    "  primary key (id),                                             -- 主键可多列\n",
    "  unique (email),\n",
    "  index ix_users_createtime_updatetime (createtime, updatetime) -- 索引\n",
    ");\n",
    "```\n",
    "\n",
    "##### 3.1.3.修改表\n",
    "\n",
    "```sql\n",
    "-- 3.1.添加一列 alter table tb_name add 列名 数据类型 修饰符\n",
    "-- 在email后面添加手机号码列\n",
    "alter table users\n",
    "  add tel varchar(20) not null;\n",
    "\n",
    "-- 3.1.1.添加含唯一键的列\n",
    "-- 先添加列\n",
    "alter table users\n",
    "  add uid bigint not null\n",
    "-- 再添加约束 alter table tb_name add constraint uq_name\n",
    "alter table users\n",
    "  add constraint uq_users_uid unique (uid); -- 自定义名称\n",
    "\n",
    "-- 3.1.2.定义和约束一步走（系统设置名字）\n",
    "-- alter table users\n",
    "--   add uid bigint not null unique; -- 默认名称\n",
    "\n",
    "-- 3.2.含唯一键的列\n",
    "-- 3.2.1.删除约束 alter table tb_name drop constraint uq_name\n",
    "if exists(select *\n",
    "          from sysobjects\n",
    "          where name = 'uq_users_uid')\n",
    "alter table users\n",
    "  drop constraint uq_users_uid;\n",
    "\n",
    "-- 3.2.2.删除列 alter table tb_name drop column 字段名\n",
    "alter table users\n",
    "  drop column uid;\n",
    "\n",
    "-- 3.3.修改字段\n",
    "-- 3.3.1.修改列名：exec sp_rename '表名.旧列名','新列名';\n",
    "exec sp_rename 'users.ucode', 'usercode';\n",
    "\n",
    "-- 3.3.2.修改字段类型\n",
    "alter table users\n",
    "    alter column username varchar(25) not null;\n",
    "\n",
    "-- 3.3.3.添加默认值：`alter table tb_name alter 列名 set default df_value`\n",
    "alter table users\n",
    "  add default '7c4a8d09ca3762af61e59520943dc26494f8941b' for password;\n",
    "```\n",
    "\n",
    "知识回顾：\n",
    "1. <a href=\"https://www.cnblogs.com/dunitian/p/5276431.html\" target=\"_blank\">01.SQLServer性能优化之---强大的文件组(分盘存储)</a>\n",
    "2. <a href=\"https://www.cnblogs.com/dunitian/p/6078512.html\" target=\"_blank\">02.SQLServer性能优化之---水平分库扩展</a>\n",
    "3. <a href=\"https://www.cnblogs.com/dunitian/p/6041745.html\" target=\"_blank\">03.SQLServer性能优化之---存储优化系列</a>\n",
    "\n",
    "课后拓展：\n",
    "```\n",
    "SQLServer突破内存限制：\n",
    "https://www.cnblogs.com/zkweb/p/6137423.html\n",
    "\n",
    "官方demo：\n",
    "https://www.microsoft.com/en-us/sql-server/developer-get-started/python/ubuntu\n",
    "\n",
    "官方文档：\n",
    "https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-overview?view=sql-server-2017\n",
    "\n",
    "PS：SQL Server默认端口为TCP 1433\n",
    "```\n",
    "\n",
    "#### 3.3.区别\n",
    "\n",
    "简单列举下上面的区别（欢迎补充）:\n",
    "1. **MySQL自增长是`auto_increment`，MSSQL是`identity`**\n",
    "2. **MySQL可以设置无符号`unsigned`，MSSQL不可以直接设置无符号整型，需要通过约束之类的来限制**\n",
    "3. **`alter table`的时候，MSSQL没有`[first | after 列名]`，而且语法差别也挺大**\n",
    "\n",
    "### 4.增删改查（CURD）\n",
    "\n",
    "#### 4.1.MySQL\n",
    "\n",
    "**select语句执行流程**：\n",
    "1. `from 表`\n",
    "2. `[inner|left|right] join 表 on 条件`\n",
    "3. `where 条件`\n",
    "    - **对select的结果进行过滤**\n",
    "4. `group by 字段`\n",
    "    - **根据指定条件把查询结果进行`分组`，以用做`聚合`运算**\n",
    "5. `having 条件`\n",
    "    - **对分组聚合运算(`group by`)后的结果进行过滤**\n",
    "6. `order by 字段 [asc|desc]`\n",
    "    - 根据指定字段对查询结果进行排序（默认升序`asc`）\n",
    "7. `select 字段`\n",
    "8. `limit [偏移量,]显示数量`\n",
    "    - 显示多少条数据 | 分页显示\n",
    "\n",
    "##### 增删改\n",
    "\n",
    "```sql\n",
    "-- 4.1.插入 help insert\n",
    "-- 自增长主键和默认值的字段可以不写\n",
    "insert into dotnetcrazy.users(username, password, email, tel, usercode, createtime, updatetime, datastatus)\n",
    "values ('dnt', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'dnt@qq.com', '18738002038', uuid(), now(), now(), 1);\n",
    "\n",
    "-- 批量插入\n",
    "insert into dotnetcrazy.users(username, password, email, tel, usercode, createtime, updatetime, datastatus)\n",
    "values('xxx', '7c4a8d09ca3762af61e59520943dc26494f8942b', 'xxx@qq.com', '13738002038', uuid(), now(), now(), 0),('mmd', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'mmd@qq.com', '13718002038', uuid(), now(), now(), 1),('小张', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'zhang@qq.com', '13728002038', uuid(), now(), now(), 1);\n",
    "\n",
    "-- 4.2.修改 help update\n",
    "update dotnetcrazy.users\n",
    "set datastatus=99,\n",
    "    updatetime = now()\n",
    "where username = 'mmd'; -- 一定要有where条件！开发中一般都是先写where条件再写update\n",
    "\n",
    "-- 4.3.删除\n",
    "-- 删除数据（自增长不重置）help delete;\n",
    "delete\n",
    "from dotnetcrazy.users\n",
    "where datastatus = 0;\n",
    "\n",
    "-- 删除全部数据（自增长重置）help truncate;\n",
    "truncate table dotnetcrazy.users;\n",
    "```\n",
    "\n",
    "##### 查询\n",
    "\n",
    "```sql\n",
    "-- 数据构造见附录\n",
    "-- 4.4.查询 help select\n",
    "\n",
    "-- 查询来源url（去重后）\n",
    "select distinct url\n",
    "from file_records;\n",
    "\n",
    "-- 查询来源url（分组方式）\n",
    "select url\n",
    "from file_records\n",
    "group by url;\n",
    "\n",
    "-- 分别统计一下url出现的次数（分组+聚合）\n",
    "-- 分组一般都和聚合函数一起使用\n",
    "select url, count(*) as count\n",
    "from file_records\n",
    "group by url;\n",
    "\n",
    "-- 分别统计一下url出现的次数，已经删除的文件不算进去\n",
    "select url, count(*) as count\n",
    "from file_records\n",
    "group by url\n",
    "having count > 3; -- 在group by的结果上筛选\n",
    "\n",
    "-- 分别统计一下url出现的次数并查出对应的id\n",
    "select group_concat(id) as ids, url\n",
    "from file_records\n",
    "group by url;\n",
    "\n",
    "-- 内连接查询 innet join tb_name on 关联条件\n",
    "select file_records.id,\n",
    "       users.id                   as uid,\n",
    "       users.username,\n",
    "       users.email,\n",
    "       file_records.file_name,\n",
    "       file_records.md5,\n",
    "       inet_ntoa(file_records.ip) as ip,\n",
    "       file_records.url\n",
    "from users\n",
    "         inner join file_records on file_records.user_id = users.id -- 连接条件\n",
    "where users.datastatus = 1\n",
    "  and file_records.datastatus = 1\n",
    "order by file_records.file_name desc; -- 文件名降序排序\n",
    "\n",
    "-- MySQL没有`select top n`语法，可以使用 limit来实现，eg：top 5\n",
    "select *\n",
    "from file_records\n",
    "limit 5; -- limit 0,5\n",
    "\n",
    "-- 分页查询\n",
    "-- page:1,count=5 ==> 0,5 ==> (1-1)*5,5\n",
    "-- page:2,count=5 ==> 5,5 ==> (2-1)*5,5\n",
    "-- page:3,count=5 ==> 10,5 ==> (3-1)*5,5\n",
    "-- 推理：limit (page-1)*count,count\n",
    "select file_records.id,\n",
    "       users.id                   as uid,\n",
    "       users.username,\n",
    "       users.email,\n",
    "       file_records.file_name,\n",
    "       file_records.md5,\n",
    "       inet_ntoa(file_records.ip) as ip,\n",
    "       file_records.url\n",
    "from file_records\n",
    "         inner join users on file_records.user_id = users.id\n",
    "limit 0,5;\n",
    "\n",
    "-- limit后面跟表达式就会报错\n",
    "select file_records.id,\n",
    "       users.id                   as uid,\n",
    "       users.username,\n",
    "       users.email,\n",
    "       file_records.file_name,\n",
    "       file_records.md5,\n",
    "       inet_ntoa(file_records.ip) as ip,\n",
    "       file_records.url\n",
    "from file_records\n",
    "         inner join users on file_records.user_id = users.id\n",
    "limit 5,5;\n",
    "-- limit (2-1)*5,5; -- limit错误写法\n",
    "\n",
    "-- limit要放在最后\n",
    "select file_records.id,\n",
    "       users.id                   as uid,\n",
    "       users.username,\n",
    "       users.email,\n",
    "       file_records.file_name,\n",
    "       file_records.md5,\n",
    "       inet_ntoa(file_records.ip) as ip,\n",
    "       file_records.url\n",
    "from file_records\n",
    "         inner join users on file_records.user_id = users.id\n",
    "order by username desc, file_name desc\n",
    "limit 10,5; -- 先order by排完序，然后再取第三页的5个数据\n",
    "\n",
    "-- 查找一下从来没上传过文件的用户\n",
    "-- right join：以右边表（users）为基准连接\n",
    "select file_records.id            as fid,\n",
    "       users.id                   as uid,\n",
    "       users.username,\n",
    "       users.email,\n",
    "       file_records.file_name,\n",
    "       file_records.md5,\n",
    "       inet_ntoa(file_records.ip) as ip,\n",
    "       file_records.url\n",
    "from file_records\n",
    "         right join users on file_records.user_id = users.id\n",
    "where users.datastatus = 1\n",
    "  and file_records.id is null\n",
    "order by username desc, file_name desc;\n",
    "\n",
    "-- 自连接案例：\n",
    "-- 二级联动 p：province，c：city，a：area\n",
    "-- 前端一般都会显示省级信息，用户选择后可以获得对应的二三级信息\n",
    "select c.name, a.name\n",
    "from city_infos as c\n",
    "         inner join city_infos as a on a.pcode = c.code\n",
    "where c.pcode = '320000'; -- pcode设置为索引\n",
    "\n",
    "-- 通过省名称查询\n",
    "select p.name, c.name, a.name\n",
    "from city_infos as c\n",
    "         inner join city_infos as p on c.pcode = p.code\n",
    "         inner join city_infos as a on a.pcode = c.code\n",
    "where p.name = '江苏省';\n",
    "```\n",
    "\n",
    "##### 视图\n",
    "\n",
    "```sql\n",
    "-- 简单提一下视图：\n",
    "-- 创建视图\n",
    "create view view_userinfo as\n",
    "select id, username, password, email, tel, datastatus\n",
    "from dotnetcrazy.users;\n",
    "\n",
    "-- 查询视图\n",
    "select id, username, password, email, tel, datastatus\n",
    "from dotnetcrazy.view_userinfo;\n",
    "\n",
    "-- 删除视图\n",
    "drop view if exists view_userinfo;\n",
    "```\n",
    "\n",
    "**附录**：\n",
    "\n",
    "知识点：\n",
    "```sql\n",
    "-- 把ip转换成int\n",
    "select inet_aton('43.226.128.3'); -- inet6_aton()\n",
    "-- 把int转换成ip\n",
    "select inet_ntoa('736264195'); -- inet6_ntoa() ipv6\n",
    "\n",
    "-- 将多个字符串连接成一个字符串\n",
    "select concat(user_id, ',', file_name, ',', ip, ',', url) as concat_str\n",
    "from file_records;\n",
    "\n",
    "-- 将多个字符串连接成一个字符串+可以一次性指定分隔符\n",
    "select concat_ws(',', user_id, file_name, ip, url) as concat_str\n",
    "from file_records;\n",
    "\n",
    "-- 在有group by的查询语句中，select指定的字段要么就包含在group by语句的后面，作为分组的依据，要么就包含在聚合函数中\n",
    "-- group_concat()：将group by产生的同一个分组中的值连接起来，返回一个字符串结果\n",
    "select group_concat(file_name) as file_name, url, count(*)\n",
    "from file_records\n",
    "group by url;\n",
    "\n",
    "-- having一般对group by的结果进行筛选，where是对原表进行筛选\n",
    "select group_concat(file_name) as file_name, group_concat(url) as url, count(*) as count\n",
    "from file_records\n",
    "group by url\n",
    "having count >= 3;\n",
    "\n",
    "-- 四舍五入到指定位数\n",
    "select round(3.12345, 4);\n",
    "-- 存小数数据为了不损伤精读一般都是转成整数，eg：3.1415 ==> 整数：31415，倍数：10000\n",
    "```\n",
    "\n",
    "**数据构造**：\n",
    "\n",
    "`city_data.sql`：<https://github.com/lotapp/BaseCode/blob/master/database/SQL/city2017.sql>\n",
    "\n",
    "```sql\n",
    "-- 编号，文件名，文件MD5，Meta(媒体类型)，当前用户，请求IP，来源地址，请求时间，数据状态\n",
    "drop table if exists file_records;\n",
    "create table if not exists file_records\n",
    "(\n",
    "    id         int unsigned auto_increment primary key,\n",
    "    file_name  varchar(100)     not null,\n",
    "    md5        char(32)         not null,\n",
    "    meta_type  tinyint unsigned not null default 1,\n",
    "    user_id    int unsigned     not null,\n",
    "    ip         int unsigned     not null,\n",
    "    url        varchar(200)     not null default '/',\n",
    "    createtime datetime         not null, -- default now(),\n",
    "    datastatus tinyint          not null default 0\n",
    ");\n",
    "\n",
    "-- 可以插入2~3次（方便下面演示）\n",
    "insert into file_records(file_name, md5, meta_type, user_id, ip, url, createtime, datastatus)\n",
    "values ('2.zip', '3aa2db9c1c058f25ba577518b018ed5b', 2, 1, inet_aton('43.226.128.3'), 'http://baidu.com', now(), 1),\n",
    "       ('3.rar', '6f401841afd127018dad402d17542b2c', 3, 3, inet_aton('43.224.12.3'), 'http://qq.com', now(), 1),\n",
    "       ('7.jpg', 'fe5df232cafa4c4e0f1a0294418e5660', 4, 5, inet_aton('58.83.17.3'), 'http://360.cn', now(), 1),\n",
    "       ('9.png', '7afbb1602613ec52b265d7a54ad27330', 5, 4, inet_aton('103.3.152.3'), 'http://cnblogs.com', now(), 1),\n",
    "       ('1.gif', 'b5e9b4f86ce43ca65bd79c894c4a924c', 6, 3, inet_aton('114.28.0.3'), 'http://qq.com', now(), 1),\n",
    "       ('大马.jsp', 'abbed9dcc76a02f08539b4d852bd26ba', 9, 4, inet_aton('220.181.108.178'), 'http://baidu.com', now(),\n",
    "        99);\n",
    "```\n",
    "\n",
    "#### 4.2.SQLServer\n",
    "\n",
    "select语句执行流程：\n",
    "1. `from 表`\n",
    "2. `join类型 join 表 on 条件`\n",
    "3. `where 条件`\n",
    "    - **对select的结果进行过滤**\n",
    "4. `group by 字段`\n",
    "    - **根据指定条件把查询结果进行`分组`，以用做`聚合`运算**\n",
    "5. `having 条件`\n",
    "    - **对分组聚合运算(`group by`)后的结果进行过滤**\n",
    "6. `select distinct 字段`\n",
    "7. `order by 字段 [asc|desc]`\n",
    "    - 根据指定字段对查询结果进行排序（默认升序`asc`）\n",
    "8. `top 多少行`\n",
    "    - 类比`limit`\n",
    "\n",
    "##### 增删改\n",
    "```sql\n",
    "-- 4.1.插入 help insert\n",
    "-- 自增长主键和默认值的字段可以不写\n",
    "insert into dotnetcrazy.dbo.users(username, password, email, tel, usercode, createtime, updatetime, datastatus)\n",
    "values ('dnt', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'dnt@qq.com', '18738002038', newid(), getdate(), getdate(),\n",
    "        1);\n",
    "\n",
    "-- 批量插入 SQLServer一次批量插入最多1000行左右\n",
    "insert into dotnetcrazy.dbo.users(username, password, email, tel, usercode, createtime, updatetime, datastatus)\n",
    "values ('xxx', '7c4a8d09ca3762af61e59520943dc26494f8942b', 'xxx@qq.com', '13738002038', newid(), getdate(), getdate(), 0),\n",
    "       ('mmd', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'mmd@qq.com', '13738002038', newid(), getdate(), getdate(), 1),\n",
    "       ('小明', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'xiaoming@qq.com', '13718002038', newid(), getdate(), getdate(), 1),\n",
    "       ('小张', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'zhang@qq.com', '13728002038', newid(), getdate(), getdate(), 1),\n",
    "       ('小潘', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'pan@qq.com', '13748002038', newid(), getdate(), getdate(), 1),\n",
    "       ('小周', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'zhou@qq.com', '13758002038', newid(), getdate(), getdate(), 1),\n",
    "       ('小罗', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'luo@qq.com', '13768002038', newid(), getdate(), getdate(), 1);\n",
    "\n",
    "-- 4.2.修改 help update\n",
    "update dotnetcrazy.dbo.users\n",
    "set datastatus=99,\n",
    "    updatetime = getdate()\n",
    "where username = 'mmd'; -- 一定要有where条件！开发中一般都是先写where条件再写update\n",
    "\n",
    "-- 4.3.删除\n",
    "-- 删除数据（自增长不重置）help delete;\n",
    "delete\n",
    "from dotnetcrazy.dbo.users\n",
    "where datastatus = 0;\n",
    "\n",
    "-- 删除全部数据（自增长重置）help truncate;\n",
    "truncate table dotnetcrazy.dbo.users;\n",
    "```\n",
    "\n",
    "##### 查询\n",
    "\n",
    "```sql\n",
    "-- 查询来源url（去重后）\n",
    "select distinct url\n",
    "from file_records;\n",
    "\n",
    "-- 查询来源url（分组方式）\n",
    "select url\n",
    "from file_records\n",
    "group by url;\n",
    "\n",
    "-- 分别统计一下url出现的次数（分组+聚合）\n",
    "-- 分组一般都和聚合函数一起使用\n",
    "select url, count(*) as count\n",
    "from file_records\n",
    "group by url;\n",
    "\n",
    "-- 分别统计一下url出现的次数，已经删除的文件不算进去\n",
    "select url, count(*) as count\n",
    "from file_records\n",
    "group by url\n",
    "having count(*) > 3; -- 在group by的结果上筛选，★写成count就不行了★\n",
    "\n",
    "-- 分别统计一下url出现的次数并查出对应的id\n",
    "-- SQLServer2017新增string_agg\n",
    "select ids =(select stuff((select ',' + cast(id as varchar(20)) from file_records as f\n",
    " where f.url = file_records.url for xml path ('')), 1, 1, '')),url from file_records\n",
    "group by url;\n",
    "\n",
    "-- 内连接查询 innet join tb_name on 关联条件\n",
    "select file_records.id,\n",
    "       users.id                   as uid,\n",
    "       users.username,\n",
    "       users.email,\n",
    "       file_records.file_name,\n",
    "       file_records.md5,\n",
    "       file_records.ip,\n",
    "       file_records.url\n",
    "from users\n",
    "         inner join file_records on file_records.user_id = users.id -- 连接条件\n",
    "where users.datastatus = 1\n",
    "  and file_records.datastatus = 1\n",
    "order by file_records.file_name desc; -- 文件名降序排序\n",
    "\n",
    "-- 显示前5个数据\n",
    "select top 5 * from file_records;\n",
    "\n",
    "-- 分页查询 第3页，每页5条\n",
    "select *\n",
    "from (select row_number() over (order by username desc, file_name desc) as id,\n",
    "             file_records.id                                            as fid,\n",
    "             users.id                                                   as uid,\n",
    "             users.username,\n",
    "             users.email,\n",
    "             file_records.file_name,\n",
    "             file_records.md5,\n",
    "             file_records.ip,\n",
    "             file_records.url\n",
    "      from file_records\n",
    "               inner join users on file_records.user_id = users.id) as temp\n",
    "where id > (3 - 1) * 5 and id <= 3 * 5;\n",
    "\n",
    "-- 简单提一下视图：\n",
    "-- 存在就删除\n",
    "if exists(select *\n",
    "          from sysobjects\n",
    "          where name = N'view_userinfo')\n",
    "    begin\n",
    "        drop view view_userinfo\n",
    "    end\n",
    "-- 创建视图\n",
    "create view view_userinfo as\n",
    "select id, username, password, email, tel, datastatus\n",
    "from users;\n",
    "\n",
    "-- 查询视图\n",
    "select id, username, password, email, tel, datastatus\n",
    "from view_userinfo;\n",
    "```\n",
    "\n",
    "##### 附录\n",
    "\n",
    "知识点：\n",
    "```sql\n",
    "select getdate() as datatime, newid() as uuid;\n",
    "\n",
    "-- 类似于concat的效果\n",
    "select cast(id as varchar(20)) + ','\n",
    "from file_records for xml path ('');\n",
    "\n",
    "-- 移除多余的字符\n",
    "-- STUFF（<character_expression>，<开始>，<长度>，<character_expression>）\n",
    "-- 将字符串插入到另一个字符串中。它会删除开始位置第一个字符串中的指定长度的字符，然后将第二个字符串插入到开始位置的第一个字符串中\n",
    "select stuff((select ',' + cast(id as varchar(20))\n",
    "              from file_records for xml path ('')), 1, 1, '');\n",
    "```\n",
    "\n",
    "数据构造：\n",
    "```sql\n",
    "--存在就删除表\n",
    "if exists(select *\n",
    "          from sysobjects\n",
    "          where name = N'file_records')\n",
    "    begin\n",
    "        drop table file_records\n",
    "    end\n",
    "-- 因为SQLServer的int没有unsigned，所以推荐使用bigint\n",
    "create table file_records\n",
    "(\n",
    "    id         bigint identity (1,1) primary key,\n",
    "    file_name  varchar(100) not null,\n",
    "    md5        char(32)     not null,\n",
    "    meta_type  tinyint      not null default 1,\n",
    "    user_id    int          not null,\n",
    "    ip         bigint       not null, -- 在程序中自行转换\n",
    "    url        varchar(200) not null default '/',\n",
    "    createtime datetime     not null default getdate(),\n",
    "    datastatus tinyint      not null default 0\n",
    ");\n",
    "\n",
    "-- 可以插入3次（方便下面演示）\n",
    "insert into file_records(file_name, md5, meta_type, user_id, ip, url, createtime, datastatus)\n",
    "values ('2.zip', '3aa2db9c1c058f25ba577518b018ed5b', 2, 1, 736264195, 'http://baidu.com', getdate(), 1),\n",
    "       ('3.rar', '6f401841afd127018dad402d17542b2c', 3, 3, 736103427, 'http://qq.com', getdate(), 1),\n",
    "       ('7.jpg', 'fe5df232cafa4c4e0f1a0294418e5660', 4, 5, 978522371, 'http://360.cn', getdate(), 1),\n",
    "       ('9.png', '7afbb1602613ec52b265d7a54ad27330', 5, 4, 1728288771, 'http://cnblogs.com', getdate(), 1),\n",
    "       ('1.gif', 'b5e9b4f86ce43ca65bd79c894c4a924c', 6, 3, 1914437635, 'http://qq.com', getdate(), 1),\n",
    "       ('大马.jsp', 'abbed9dcc76a02f08539b4d852bd26ba', 9, 4, 3702877362, 'http://baidu.com', getdate(), 99);\n",
    "```\n",
    "\n",
    "### 5.MySQL命令扩展：\n",
    "\n",
    "1. **命令帮助**：`MySQL>` **`help 命令`**\n",
    "    - PS：版本查询：`select version();`\n",
    "2. 查看字符集：**`show character set;`**\n",
    "    - **utf8**：使用1~3bytes来表示一个Unicode字符（常用）\n",
    "    - **utf8mb4**：使用1~4bytes来表示一个Unicode字符（`Emoji表情` or `不常用汉字`）\n",
    "3. 排序规则：`show collation;`\n",
    "    - eg：`show collation where Collation like \"%utf8%\";`\n",
    "4. 查看引擎：`show engines;`\n",
    "    - `InnoDB是默认存储引擎`\n",
    "5. **查看所有数据库：`show databases;`**\n",
    "6. **切换数据库：`use db_name;`**\n",
    "7. **查看所有表：`show tables;`**\n",
    "8. **显示表状态：`show table status;`**\n",
    "    - eg：`show table status like 'users';`\n",
    "9. **显示表结构：`desc tb_name;`**\n",
    "10. **查看创建表时的SQL：`show create table tb_name;`**\n",
    "11. **显示表的索引：`show indexes from tb_name`**\n",
    "12. 查看mysql数据文件目录`show variables like '%dataDir%';`\n",
    "13. 查询当前会话的连接号：`select connection_id();`\n",
    "\n",
    "**PS：`\\G可以竖排显示`：`show table status like 'users'\\G`**\n",
    "\n",
    "最后YY几句：\n",
    "1. 没使用`Linux`之前，我认为`C#是最优美、性价比最高、最简单的语言`，之后发现`Python才是最简单的语言`，`C#只能是最优美、性价比最高的语言`\n",
    "    - 现在准备接触Golang，最终评价先待定吧\n",
    "2. 刚接触MySQL发现SQLServer真的很方便，研究MySQL越深越发现==>平心而讲：\n",
    "    - **对应开发人员来说，`MySQL`真的比`SQLServer`方便**\n",
    "    - **对于运维人员来说，`SQLServer`真的太方便了**\n",
    "    - PS：中小企业如果没有专门运维人员，还是推荐`SQLServer`，如果有运维人员或者团队有点`Linux`运维功底的还是选择`MySQL`吧\n",
    "\n",
    "送大家一句话：**`思维局限在一个维度里，认知就会发生偏移，希望大家能够勇于尝试和突破~`**\n",
    "\n",
    "因为时间问题之后的SQL案例就不对比演示了，直接全部`MySQL`走起（之后只能说尽量加上`SQLServer`版的演示）\n",
    "\n",
    "**下节预估：查询优化**\n",
    "\n",
    "课外拓展：\n",
    "```\n",
    "MySQL在线IDE：phpMyAdmin\n",
    "https://www.phpmyadmin.net/downloads/\n",
    "\n",
    "MySQL最火工具：Navicat Premium\n",
    "https://www.cnblogs.com/dotnetcrazy/p/9711198.html\n",
    "\n",
    "MySQL最佳工具：dbForge Studio for MySQL\n",
    "https://www.devart.com/dbforge/mysql/studio/download.html\n",
    "\n",
    "【跨平台】SQLServer工具：SqlOps\n",
    "https://www.cnblogs.com/dunitian/p/8045081.html\n",
    "https://github.com/Microsoft/azuredatastudio/releases\n",
    "\n",
    "【跨平台】都支持：JetBrains DataGrip 【推荐】\n",
    "https://www.cnblogs.com/dotnetcrazy/p/9711763.html\n",
    "\n",
    "MariaDB数据类型\n",
    "https://www.w3cschool.cn/mariadb/mariadb_data_types.html\n",
    "\n",
    "MySQL 数据类型\n",
    "https://www.w3cschool.cn/mysql/mysql-data-types.html\n",
    "\n",
    "(MariaDB)MySQL数据类型详解和存储机制\n",
    "https://www.cnblogs.com/f-ck-need-u/archive/2017/10/25/7729251.html\n",
    "\n",
    "Sql Server中的数据类型和Mysql中的数据类型的对应关系\n",
    "https://blog.csdn.net/lilong329329/article/details/78899477\n",
    "\n",
    "ALTER TABLE和CREATE INDEX的区别\n",
    "https://blog.csdn.net/qq_34578253/article/details/72236808\n",
    "1. create index必须提供索引名，对于alter table，如果你不提供索引名称，MySQL会自动创建索引名称（默认为第一个列名）\n",
    "2. create index一个语句一次只能建立一个索引，alter table可以在一个语句建立多个，如：\n",
    "    - `ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);`\n",
    "3. 只有alter table才能创建主键\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.5.查询的艺术\n",
    "\n",
    "上期回顾：<https://www.cnblogs.com/dotnetcrazy/p/10399838.html>\n",
    "\n",
    "本节脚本：<https://github.com/lotapp/BaseCode/blob/master/database/SQL/02.索引、查询优化.sql>\n",
    "\n",
    "文章有点小长，但认真阅读肯定会有所感触和收获的。PS：我把我能想到的都列下来了，如果有新的会追加，欢迎补充和纠错~\n",
    "\n",
    "### 1.5.1.索引\n",
    "\n",
    "大方向：**减少冗余索引，避免重复（无用）索引**\n",
    "\n",
    "#### 1.概念\n",
    "\n",
    "大一统分类：\n",
    "1. 聚簇索引、非聚簇索引：看看数据是否与索引存储在一起（一起是聚簇索引）\n",
    "2. 主键索引、辅助索引\n",
    "3. 稠密索引、稀疏索引\n",
    "    - 是否索引了每一个数据项（是则为稠密索引）\n",
    "4. `B+ Tree`索引、`hash`索引（键值索引，`只有Memory存储引擎支持`）、`R Tree`索引（空间索引，`MyISAM存储引擎支持`）、`Fulltext`索引（全文索引）\n",
    "2. 简单索引、组合索引\n",
    "\n",
    "PS：索引通常做查询条件的字段（索引是在存储引擎级别实现的）\n",
    "\n",
    "**常用分类：**\n",
    "1. 语法分类：\n",
    "    1. **普通索引**：一列一索引\n",
    "    2. **唯一索引**：设置unique之后产生（可空）\n",
    "        - 可以这么理解：唯一+非空=主键\n",
    "    3. **复合索引**：多列一索引\n",
    "2. 物理存储：（Innodb和MyISAM存储引擎）\n",
    "    1. **聚簇索引**：一般都是主键\n",
    "        - 数据和索引存储在一起的存储方式\n",
    "        - Innodb文件后缀：frm、ibd（数据+索引）\n",
    "    2. **非聚簇索引**：不是聚集索引的索引\n",
    "        - 数据和索引分开存放\n",
    "        - MyISAM文件后缀：frm、myd（数据）、myi（索引）\n",
    "    3. PS：它俩都是b树索引，frm（表结构）和存储引擎无关\n",
    "\n",
    "#### 2. 语法基础\n",
    "\n",
    "1. 查看索引：`show index from tb_name;`\n",
    "    - show index from worktemp.userinfo\\G;\n",
    "    - show index from worktemp.userinfo;\n",
    "2. 创建索引：\n",
    "    - `create [unique] index index_name on tb_name(列名,...)`\n",
    "    - `alter table tb_name add [unique] index [index_name] on (列名,...)`\n",
    "3. 删除索引：\n",
    "    - `drop index index_name on tb_name`\n",
    "    - `alter table tb_name drop index index_name`\n",
    "\n",
    "### 1.5.2.执行计划\n",
    "\n",
    "#### 1.往期回顾\n",
    "\n",
    "先回顾下上节课内容：\n",
    "\n",
    "**手写SQL的语法顺序：**\n",
    "```sql\n",
    "select distinct\n",
    "    <select_list>\n",
    "from <tb_name>\n",
    "    <join_type> join <right_table> on <join_condition>\n",
    "where\n",
    "    <where_condition>\n",
    "group by\n",
    "    <group_by_list>\n",
    "having\n",
    "    <having_condition>\n",
    "order by\n",
    "    <order_by_list>\n",
    "limit <limit_number>\n",
    "```\n",
    "\n",
    "**SQL执行顺序：**\n",
    "1. `from <tb_name>`\n",
    "2. `on <join_condition>`\n",
    "3. `<join_type> join <right_table>`\n",
    "4. `where <where_condition>`\n",
    "5. `group by <group_by_list>`\n",
    "6. `having <having_condition>`\n",
    "7. `select [distinct] <select_list>`\n",
    "8. `order by <order_by_list>`\n",
    "9. `limit <limit_number>`\n",
    "\n",
    "#### 2.基础\n",
    "\n",
    "语法：explain + SQL语句\n",
    "\n",
    "执行计划：**使用`explain`关键词可以模拟优化器执行SQL查询语句，一般用来`分析查询语句或者表结构的性能瓶颈`**\n",
    "\n",
    "执行计划一般用来干这些事情：\n",
    "1. 查看表的读取顺序\n",
    "2. 查看数据读取操作的操作类型\n",
    "3. 查看哪些索引可以使用\n",
    "4. 查看哪些索引被实际使用\n",
    "5. 查看表之间的引用\n",
    "6. 查看每张表有多少行被优化器读取\n",
    "\n",
    "##### 主要参数\n",
    "\n",
    "主要是看这几个参数：\n",
    "1. id：当前查询语句中，每个select语句的编号\n",
    "    - 主要是针对子查询、union查询\n",
    "2. `select_type`：查询类型\n",
    "    - 简单查询：simple（一般的查询语句）\n",
    "    - 复杂查询：（详解见附录1）\n",
    "        - `subquery`：用于where中的子查询（简单子查询）\n",
    "        - `derived`：用于from中的子查询\n",
    "        - `union`：union语句的第一个之后的select语句\n",
    "        - `union result`：匿名临时表\n",
    "3. `type`：访问类型(MySQL查询表中行的方式)\n",
    "    1. all：全表扫描\n",
    "    2. index：根据索引的次序进行全表扫描（**覆盖索引效率更高**）\n",
    "    3. range：根据索引做指定范围扫描\n",
    "    4. ref：返回表中所有匹配某单个值的所有行\n",
    "    5. eq_ref：等同于ref，与某个值做比较且仅返回一行\n",
    "    6. const：根据具有唯一性索引查找时，且返回单个行（**性能最优**）\n",
    "        - eg：主键、唯一键\n",
    "    7. **PS：1~6 ==> 数字越大效率越高（性能递增）**，（详解见附录2）\n",
    "4. `possible_keys`：查询可能会用到的索引\n",
    "5. `key`：查询中使用了的索引\n",
    "6. `key_len`：索引使用的字节数（详解见附录3）\n",
    "    - 根据这个值，可以判断索引使用情况\n",
    "    - eg：使用组合索引时，判断所有索引字段是否都被查询到\n",
    "7. `ref`：显示key列索引用到了哪些列、常量值\n",
    "    - 在索引列上查找数据时，用到了哪些列或者常量\n",
    "8. `rows`：估算大概需要扫描多少行\n",
    "9. `Extra`：额外信息（性能递减）\n",
    "    1. **using index**：使用了覆盖索引\n",
    "    2. `using where`：在存储引擎检索后，再进行一次过滤\n",
    "    3. using temporary：对结果排序时会使用临时表\n",
    "    4. using filesort：对结果使用一个外部索引排序\n",
    "        - 没有有索引顺序，使用了自己的排序算法\n",
    "        - 可能出现的情况：（**出现这个情况基本上都是需要优化的**）\n",
    "            - where后面的索引列和`order by|group by`后面的索引列不一致(只能用到一个索引)\n",
    "            - eg：`explain select * from users where id<10 order by email;`（只用到了id）\n",
    "\n",
    "#### 附录\n",
    "\n",
    "##### 1.select_type\n",
    "\n",
    "**`select_type`：查询类型**\n",
    "```sql\n",
    "-- `subquery`：用于where中的子查询（简单子查询）\n",
    "explain\n",
    "    select name, age\n",
    "    from students\n",
    "    where age > (select avg(age) from students);\n",
    "\n",
    "-- `union`：union语句的第一个之后的select语句\n",
    "-- `union result`：匿名临时表\n",
    "explain\n",
    "    select name, age, work\n",
    "    from students\n",
    "    where name = '小张'\n",
    "    union\n",
    "    select name, age, work\n",
    "    from students\n",
    "    where name = '小明';\n",
    "\n",
    "-- `derived`：用于from中的子查询\n",
    "explain\n",
    "    select *\n",
    "    from (select name, age, work from students where name = '小张'\n",
    "          union\n",
    "          select name, age, work from students where name = '小明') as tmp;\n",
    "```\n",
    "图示输出：\n",
    "![1.sql_type.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222093813306-585596118.png)\n",
    "\n",
    "##### 2.type\n",
    "\n",
    "**`type`：访问类型(MySQL查询表中行的方式)**\n",
    "```sql\n",
    "-- all：全表扫描（效率极低）\n",
    "explain\n",
    "    select *\n",
    "    from students\n",
    "    where name like '%小%';\n",
    "\n",
    "-- index：根据索引的次序进行全表扫描（效率低）\n",
    "explain\n",
    "    select name, age, work\n",
    "    from students\n",
    "    where name like '%小%'; -- 其实就是上面全表扫描的改进版\n",
    "\n",
    "-- range：根据索引做指定范围扫描\n",
    "explain\n",
    "    select name, age, work\n",
    "    from students\n",
    "    where id > 5;\n",
    "\n",
    "-- ref：返回表中所有匹配某单个值的所有行\n",
    "explain\n",
    "    select name, age, work\n",
    "    from students\n",
    "    where name = '小明';\n",
    "\n",
    "-- eq_ref：等同于ref，与某个值做比较且仅返回一行\n",
    "explain\n",
    "    select *\n",
    "    from userinfo\n",
    "             inner join (select id from userinfo limit 10000000,10) as tmp\n",
    "                        on userinfo.id = tmp.id; -- 1s\n",
    "\n",
    "-- const：根据具有唯一性索引查找时，且返回单个行（**性能最优**）\n",
    "explain\n",
    "    select name, age, work\n",
    "    from students\n",
    "    where id = 3; -- 一般都是主键或者唯一键\n",
    "```\n",
    "图示输出：\n",
    "\n",
    "![2.type1.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222100652656-431470082.png)\n",
    "![2.type2.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222100658975-370830430.png)\n",
    "\n",
    "##### 3.key-len\n",
    "\n",
    "1. 是否为空：\n",
    "    - not null 不需要额外的字节\n",
    "    - null 需要1字节用来标记\n",
    "    - PS：索引最好不要为null，这样需要额外的存储空间而且统计也变得更复杂\n",
    "2. 字符类型（char、varchar）的索引长度计算：\n",
    "    - 字符编码：(PS：不同字符编码占用的存储空间不同)\n",
    "        - `latin1`|`ISO8859`占1个字节，`gbk`占2个字节，**`utf8`占3个字节**\n",
    "    - 变长字段（varchar）需要额外的2个字节\n",
    "        - 1字节用来保存需要的字符数\n",
    "        - 1字节用来记录长度（PS：如果列定义的长度超过255则需要2个字节【总共3字节】）\n",
    "    - 定长字段（char）不需要额外的字节\n",
    "3. 数值类型、日期类型的索引长度计算：\n",
    "    - 一般都是其本身长度，如果可空则+1\n",
    "        - 标记是否为空需要占1个字节\n",
    "    - PS：datetime在5.6中字段长度是5，在5.5中字段长度是8\n",
    "4. 复合索引有最左前缀的特性。如果复合索引能全部用上，则为复合索引字段的索引长度之和\n",
    "    - PS：可以用来判断复合索引是否全部使用到\n",
    "5. 举个栗子：\n",
    "    - eg：`char(20) index 可空`\n",
    "        - `key-len=20*3(utf8)+1(可空)=61`\n",
    "    - eg：`varchar(20) index 可空`\n",
    "        - `key-len=20*3(utf8)+2(可变长度)+1(是否可空的标记)=63`\n",
    "\n",
    "##### 建表语句\n",
    "```sql\n",
    "create table if not exists `students`\n",
    "(\n",
    "    id          int unsigned auto_increment primary key,\n",
    "    name        varchar(25)      not null default '' comment '姓名',\n",
    "    age         tinyint unsigned not null default 0 comment '年龄',\n",
    "    work        varchar(20)      not null default '普通学生' comment '职位',\n",
    "    create_time datetime         not null comment '入学时间',\n",
    "    datastatus  tinyint          not null default 0 comment '数据状态'\n",
    ") charset utf8 comment '学生表';\n",
    "\n",
    "-- select current_timestamp(), now(), unix_timestamp();\n",
    "insert into students(name, age, work, create_time, datastatus)\n",
    "values ('111', 22, 'test', now(), 99),\n",
    "       ('小张', 23, '英语课代表', now(), 1),\n",
    "       ('小李', 25, '数学课代表', now(), 1),\n",
    "       ('小明', 21, '普通学生', now(), 1),\n",
    "       ('小潘', 27, '物理课代表', now(), 1),\n",
    "       ('张小华', 22, '生物课代表', now(), 1),\n",
    "       ('张小周', 22, '体育课代表', now(), 1),\n",
    "       ('小罗', 22, '美术课代表', now(), 1);\n",
    "\n",
    "-- 创建一个组合索引\n",
    "create index ix_students_name_age_work on students (name, age, work);\n",
    "```\n",
    "\n",
    "说了这么多题外话，现在进入正题：\n",
    "\n",
    "---\n",
    "\n",
    "### 1.5.3.建表优化\n",
    "\n",
    "1. 定长和变长分离（具体得看业务）\n",
    "    - eg：varchar、text、blob等变长字段单独出一张表和主表关联起来即可\n",
    "2. 常用字段和不常用字段分离\n",
    "    - 根据业务来分析，不常用的字段拎出来\n",
    "3. 在1对多需要关联统计的字段上添加点冗余字段\n",
    "    - 分表分库时，扩表跨库查询的情景（注意数据一致性）\n",
    "    - eg：在分类表中添加一个数量字段，统计每天新增商品数量\n",
    "        - 添加商品时，选完分类就update一下count值（第二天清零）\n",
    "4. 字段类型一般都是按照这个优先级：（尽量使用优先级高的类型）\n",
    "    - `数值 > 日期 > char > varchar > text、blob`\n",
    "    - PS：总体原则就是够用即可，然后尽量避免null（不利于索引，浪费空间）\n",
    "        - eg：varchar(10)和varchar(300)，在表连接查询时，需要的内存是不一样的\n",
    "5. **伪hash法**：比如商品url是一个varchar的列\n",
    "    - 这时候再建一个hash(url)之后的列，把索引设置到该列\n",
    "        - 推荐使用**`crc32`**（用bigint存储）索引空间就会小很多而且可以避免全表扫描\n",
    "        - eg：`select crc32('http://www.baidu.com/shop/1.html');`\n",
    "    - PS：如果DBA配置了crc64，则使用；如果没有，可以加个条件（`CRC32碰撞后的解决方案`）\n",
    "        - 对于少部分碰撞的记录，只需要多扫描几行就行了，不会出现全表扫描的情况\n",
    "        - eg：`select xxx from urls where crc_url=563216577 and url='url地址'`\n",
    "\n",
    "**PS：需要关注的技术点：`crc32`**\n",
    "\n",
    "### 1.5.4.组合索引专题\n",
    "\n",
    "项目里面使用最多的是组合索引，这边先以组合索引为例：\n",
    "\n",
    "#### 1.尽可能多的使用索引列，尽可能使用覆盖索引\n",
    "\n",
    "```sql\n",
    "-- 如果我查询的时候，索引的三列都用到了，那么速度无疑是最快的\n",
    "-- Extra：using where\n",
    "explain\n",
    "    select id, name, age, work, create_time\n",
    "    from students\n",
    "    where name = '小张'\n",
    "      and age = 23\n",
    "      and work = '英语课代表';\n",
    "\n",
    "-- PS：★尽量使用覆盖索引★（近乎万能）\n",
    "-- 覆盖索引：仅仅查找索引就能找到所需要的数据\n",
    "-- Extra：using where;using index\n",
    "explain\n",
    "    select name, age, work\n",
    "    from students\n",
    "    where name = '小张'\n",
    "      and age = 23\n",
    "      and work = '英语课代表';\n",
    "-- PS：一般把经常select出的列设置一个组合索引，一般不超过5个\n",
    "```\n",
    "图示：\n",
    "![3.1.覆盖索引.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222102416893-1333428547.png)\n",
    "\n",
    "#### 2.最左前缀原则\n",
    "\n",
    "类比火车，火车头自己可以开，车身要是没有了车头就开不了\n",
    "```sql\n",
    "-- 查询的时候从最左边的列开始，并且不跳过中间的列，一直到最后\n",
    "explain\n",
    "    select id, name, age, work, create_time\n",
    "    from students\n",
    "    where name = '小张'\n",
    "      and age = 23\n",
    "      and work = '英语课代表';\n",
    "\n",
    "-- 跳过了中间的age，这时候只用到了name列的索引（work列没用到）\n",
    "explain\n",
    "    select id, name, age, work, create_time\n",
    "    from students\n",
    "    where name = '小张'\n",
    "      and work = '英语课代表';\n",
    "```\n",
    "图示：\n",
    "![3.2.组合索引失效.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222102955040-1112118122.png)\n",
    "\n",
    "再看两个补充案例：\n",
    "```sql\n",
    "-- PS：如果跳过了第一列，这时候索引一个也用不到，直接全表扫描了\n",
    "explain\n",
    "    select id, name, age, work, create_time\n",
    "    from students\n",
    "    where age = 23\n",
    "      and work = '英语课代表';\n",
    "\n",
    "-- PS：列不一定需要按照指定顺序来写\n",
    "explain\n",
    "    select id, name, age, work, create_time\n",
    "    from students\n",
    "    where age = 23\n",
    "      and work = '英语课代表'\n",
    "      and name = '小张';\n",
    "```\n",
    "图示：\n",
    "![3.2.组合索引失效2.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222103352698-972291023.png)\n",
    "\n",
    "#### 2.3.范围条件放在最后面（范围条件后面的列索引会失效）\n",
    "\n",
    "```sql\n",
    "-- name、age、work索引生效时，key_len=140\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name = '小张'\n",
    "      and age = 23\n",
    "      and work = '英语课代表';\n",
    "\n",
    "-- 现在key_len=78 ==> work列索引就失效了（PS：age索引列未失效，只是age之后的列失效了）\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name = '小张'\n",
    "      and age > 22\n",
    "      and work = '英语课代表';\n",
    "```\n",
    "图示：\n",
    "![3.3.范围后面索引失效.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222103708747-1430799785.png)\n",
    "\n",
    "补充说明：\n",
    "```sql\n",
    "-- 加快查询速度可以使用覆盖索引\n",
    "explain\n",
    "    select name, age, work\n",
    "    from students\n",
    "    where name = '小张'\n",
    "      and age > 22\n",
    "      and work = '英语课代表';\n",
    "\n",
    "-- PS：多个主键列也一样\n",
    "explain\n",
    "    select id, name, age, work\n",
    "    from students\n",
    "    where name = '小张'\n",
    "      and age > 22\n",
    "      and work = '英语课代表';\n",
    "\n",
    "-- PS：调换顺序是没法解决范围后面索引失效的（本来对顺序就不在意）\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name = '小张'\n",
    "      and work = '英语课代表'\n",
    "      and age > 22;\n",
    "```\n",
    "图示：\n",
    "![3.3.范围后面索引失效2.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222104913442-1073722143.png)\n",
    "\n",
    "#### 2.4.不在索引列上做其他操作\n",
    "\n",
    "容易导致全表扫描，这时候利用覆盖索引可以简单优化下\n",
    "\n",
    "##### 1.`!=`、`is not null`、`is null`、`not in`、`in`、`like`慎用\n",
    "\n",
    "**`!=`、`is not null`、`is null`的案例**\n",
    "```sql\n",
    "-- 1.不等于案例\n",
    "-- 索引失效（key,key_len ==> null）\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name != '小明'; -- <> 等同于 !=\n",
    "\n",
    "-- 项目里面很多使用都要使用，那怎么办呢？==> 使用覆盖索引\n",
    "-- key=ix_students_name_age_work，key_len=140\n",
    "explain\n",
    "    select name, age, work\n",
    "    from students\n",
    "    where name != '小明'; -- <> 等同于 !=\n",
    "\n",
    "-- 2.is null、is not null案例\n",
    "-- 索引失效（key,key_len ==> null）\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name is not null;\n",
    "\n",
    "-- 解决：覆盖索引 key=ix_students_name_age_work，key_len=140\n",
    "explain\n",
    "    select name, age, work\n",
    "    from students\n",
    "    where name is not null;\n",
    "```\n",
    "图示：\n",
    "![3.4.不等于和null.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222105633440-1416658067.png)\n",
    "\n",
    "**`not in`、`in`的案例**\n",
    "```sql\n",
    "-- 3.not in、in案例\n",
    "-- 索引失效（key,key_len ==> null）\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name in ('小明', '小潘', '小李');\n",
    "\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name not in ('小明', '小潘', '小李');\n",
    "\n",
    "-- 解决：覆盖索引 key=ix_students_name_age_work，key_len=140\n",
    "explain\n",
    "    select name, age, work\n",
    "    from students\n",
    "    where name in ('小明', '小潘', '小李');\n",
    "\n",
    "explain\n",
    "    select name, age, work\n",
    "    from students\n",
    "    where name not in ('小明', '小潘', '小李');\n",
    "```\n",
    "图示：\n",
    "![3.5.in和notin的案例.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222110159756-2035131911.png)\n",
    "\n",
    "**`like`案例**：尽量使用`xxx%`的方式来全文搜索，能和覆盖索引联合使用更好\n",
    "```sql\n",
    "-- 4.like案例\n",
    "-- 索引不失效 key=ix_students_name_age_work，key_len=77（尽量这么用like）\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name like '张%';\n",
    "\n",
    "-- 索引失效\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name like '%张';\n",
    "\n",
    "-- 索引失效\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name like '%张%';\n",
    "\n",
    "-- 解决：覆盖索引 key=ix_students_name_age_work，key_len=140（尽量避免）\n",
    "explain\n",
    "    select name, age, work\n",
    "    from students\n",
    "    where name like '%张%';\n",
    "```\n",
    "![3.6.like案例.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222110658807-1141718691.png)\n",
    "\n",
    "##### 2.计算、函数、类型转换（自动 or 手动）【尽量避免】\n",
    "\n",
    "```sql\n",
    "-- 4.2.计算、函数、类型转换（自动 or 手动）【尽量避免】\n",
    "-- 这时候索引直接失效了，并全表扫描了\n",
    "-- 解决虽然可以使用覆盖索引，但是尽量避免下面的情况：\n",
    "-- 1.计算\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where age = (10 + 13);\n",
    "\n",
    "-- 2.隐式类型转换（111==>'111'）\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name = 111;\n",
    "-- PS：字符类型不加引号索引就直接失效了\n",
    "-- 虽然覆盖索引可以解决，但是不要这样做（严格意义上讲，这个算个错误）\n",
    "\n",
    "-- 3.函数\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where right(name, 1) = '明';\n",
    "```\n",
    "图示：\n",
    "![3.7.其他案例.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222111309886-1074328211.png)\n",
    "\n",
    "---\n",
    "\n",
    "光看没意思，再举个简单的业务案例：\n",
    "> eg：用户一般都是根据商品的大分类=>小分类=>品牌来查找，有时候到不看品牌，直接小分类后就自己找了。那么组合索引可以这么建：`index(分类id,商品价格)`,`index(分类id,品牌id,商品价格)`(一般都需要根据查询日记来确定)\n",
    "\n",
    "PS：有些条例是流传甚广的，有些是工作中的经验，至少都是我踩过坑的，可以相对放心（业务不同优化角度不同）\n",
    "\n",
    "### 1.5.5.写法上的优化\n",
    "\n",
    "#### 5.1.or改成union\n",
    "\n",
    "```sql\n",
    "-- 5.1.or改成union\n",
    "-- 现在高版本对只有一个or的sql语句有了优化\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name = '小明'\n",
    "       or name = '小张'\n",
    "       or name = '小潘';\n",
    "\n",
    "-- PS：等同上面or的语句\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name in ('小明', '小张', '小潘');\n",
    "\n",
    "-- 高效\n",
    "explain\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name = '小明'\n",
    "    union all\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name = '小张'\n",
    "    union all\n",
    "    select id, name, age, work, create_time, datastatus\n",
    "    from students\n",
    "    where name = '小潘';\n",
    "\n",
    "```\n",
    "![4.union.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222115725953-50703339.png)\n",
    "\n",
    "**PS：union总是产生临时表，优化起来比较棘手**\n",
    "> 一般来说union子句尽量查询最少的行，union子句在内存中合并结果集需要去重（浪费资源），所以**使用union的时候尽量加上all**（在程序级别去重即可）\n",
    "\n",
    "#### 5.2.count优化\n",
    "\n",
    "一般都是`count(主键|索引)`，但现在`count(*)`基本上数据库内部都优化过了（根据公司要求使用即可）\n",
    "> PS：记得当时踩了次坑，等复现的时候补上案例（记得好像跟null相关）\n",
    "\n",
    "看下就知道为什么说无所谓了（PS，你`count(非索引)`就有所谓了）\n",
    "```sql\n",
    "explain\n",
    "    select count(id) -- 常用\n",
    "    from userinfo;\n",
    "\n",
    "explain\n",
    "    select count(*)\n",
    "    from userinfo;\n",
    "\n",
    "-- 你`count(非索引)`就有所谓了\n",
    "explain\n",
    "    select count(password)\n",
    "    from userinfo;\n",
    "```\n",
    "![4.2.count.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222120802567-384758813.png)\n",
    "\n",
    "**我想说的优化是下面这个count优化案例：**（有时候拆分查询会更快）\n",
    "```sql\n",
    "-- 需要统计id>10000的数据总量（实际中可能会根据时间来统计）\n",
    "explain\n",
    "    select count(*) as count\n",
    "    from userinfo\n",
    "    where id > 10000; -- 2s\n",
    "\n",
    "-- 分解成用总数-小数据统计 ==> 1s\n",
    "explain\n",
    "    select (select count(*) from userinfo) - (select count(*) from userinfo where id <= 10000) as count;\n",
    "```\n",
    "执行图示：\n",
    "![4.2.count2.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222122113495-127359791.png)\n",
    "\n",
    "分析图示：\n",
    "![4.2.count3.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222122256578-190247523.png)\n",
    "\n",
    "#### 5.3.group by和order by\n",
    "\n",
    "**`group by`和`order by`的列尽量相同，这样可以避免filesort**\n",
    "```sql\n",
    "-- 5.3.group by和order by的列尽量相同，这样可以避免filesort\n",
    "explain\n",
    "    select *\n",
    "    from students\n",
    "    group by name\n",
    "    order by work;\n",
    "\n",
    "explain\n",
    "    select *\n",
    "    from students\n",
    "    group by name\n",
    "    order by name;\n",
    "\n",
    "-- 加where条件也一样\n",
    "explain\n",
    "    select *\n",
    "    from students\n",
    "    where name like '小%'\n",
    "    group by age\n",
    "    order by work;\n",
    "\n",
    "-- PS：一般group by和order by的列都和where索引列相同（不一致也只会使用一个索引）\n",
    "explain\n",
    "    select *\n",
    "    from students\n",
    "    where name like '小%' and age>20\n",
    "    group by name\n",
    "    order by name;\n",
    "\n",
    "-- where后面的索引列和`order by|group by`后面的索引列不一致\n",
    "-- id和email都是索引，但只用了一个索引\n",
    "explain\n",
    "    select *\n",
    "    from users\n",
    "    where id < 10\n",
    "    order by email;\n",
    "```\n",
    "图示：\n",
    "![4.3.orderby.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222123205328-1820586982.png)\n",
    "\n",
    "**PS：不一致也只会使用一个索引（在索引误区有详细说明）**\n",
    "\n",
    "#### 5.4.用连接查询来代替子查询\n",
    "\n",
    "**一般来说都是用连接查询来代替子查询**，有些时候子查询更方便（具体看业务吧）\n",
    "\n",
    "```sql\n",
    "-- 用exists代替in？MySQL查询优化器针对in做了优化（改成了exists，当users表越大查询速度越慢）\n",
    "explain\n",
    "    select *\n",
    "    from students\n",
    "    where name in (select username from users where id < 7);\n",
    "\n",
    "-- ==> 等同于：\n",
    "explain\n",
    "    select *\n",
    "    from students\n",
    "    where exists(select username from users where username = students.name and users.id < 7);\n",
    "\n",
    "-- 真正改进==>用连接查询代替子查询\n",
    "explain\n",
    "    select students.*\n",
    "    from students\n",
    "             inner join users on users.username = students.name and users.id < 7;\n",
    "\n",
    "-- 等效写法：这个tmp是临时表，是没有索引的，如果需要排序可以在（）里面先排完序\n",
    "explain\n",
    "    select students.*\n",
    "    from students\n",
    "             inner join (select username from users where id < 7) as tmp on students.name = tmp.username;\n",
    "```\n",
    "图示：(内部已经把in转换成exists了，所以改不改写无所谓了)\n",
    "![4.4.子查询.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222135505711-891139395.png)\n",
    "\n",
    "#### 5.5.★limit优化★\n",
    "\n",
    "**`limit offset,N`**：mysql并不是跳过`offset`行，然后取`N`行，而是取`offset+N`行，然后放弃前`offset`行，返回`N`行\n",
    "- PS：`offset越大效率越低`（你去翻贴的时候，页码越大一般越慢）\n",
    "\n",
    "##### 知识点 ~ profiling\n",
    "\n",
    "为了更加的直观，我们引入一下**`profiling`**\n",
    "```sql\n",
    "-- 查看profiling系统变量\n",
    "show variables like '%profil%';\n",
    "-- profiling：开启SQL语句剖析功能（开启之后应为ON）\n",
    "\n",
    "-- 来查看是否已经启用profile\n",
    "select @@profiling;\n",
    "\n",
    "-- 启动profile（当前会话启动）\n",
    "set profiling = 1; -- 0：未启动，1：启动\n",
    "\n",
    "show profiles; -- 显示查询的列表\n",
    "\n",
    "show profile for query 5; -- 查看指定编号查询的详细信息\n",
    "```\n",
    "输出：\n",
    "```\n",
    "MariaDB [dotnetcrazy]> show variables like '%profil%';\n",
    "+------------------------+-------+\n",
    "| Variable_name          | Value |\n",
    "+------------------------+-------+\n",
    "| have_profiling         | YES   |\n",
    "| profiling              | OFF   |\n",
    "| profiling_history_size | 15    |\n",
    "+------------------------+-------+\n",
    "3 rows in set (0.002 sec)\n",
    "\n",
    "MariaDB [dotnetcrazy]> select @@profiling;\n",
    "+-------------+\n",
    "| @@profiling |\n",
    "+-------------+\n",
    "|           0 |\n",
    "+-------------+\n",
    "1 row in set (0.000 sec)\n",
    "\n",
    "MariaDB [dotnetcrazy]> set profiling = 1;\n",
    "Query OK, 0 rows affected (0.000 sec)\n",
    "```\n",
    "\n",
    "##### 正文\n",
    "\n",
    "上面设置完后，分别执行下面SQL：\n",
    "```sql\n",
    "select * from userinfo limit 10,10;\n",
    "select * from userinfo limit 1000,10;\n",
    "select * from userinfo limit 100000,10;\n",
    "select * from userinfo limit 1000000,10;\n",
    "select * from userinfo limit 10000000,10;\n",
    "```\n",
    "输出：\n",
    "```\n",
    "+----------+------------+------------------------------------------+\n",
    "| Query_ID | Duration   | Query                                    |\n",
    "+----------+------------+------------------------------------------+\n",
    "|        1 | 0.00060250 | select * from userinfo limit 10,10       |\n",
    "|        2 | 0.00075870 | select * from userinfo limit 1000,10     |\n",
    "|        3 | 0.03121300 | select * from userinfo limit 100000,10   |\n",
    "|        4 | 0.30530230 | select * from userinfo limit 1000000,10  |\n",
    "|        5 | 3.03068020 | select * from userinfo limit 10000000,10 |\n",
    "+----------+------------+------------------------------------------+\n",
    "```\n",
    "图示：\n",
    "![4.4.profiles.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222124518563-1739543720.png)\n",
    "\n",
    "##### 解决方法\n",
    "1. 业务上解决，eg：不许翻页超过100（一般都是通过搜索来查找数据）\n",
    "    - PS：百度搜索页面也只是最多翻到76\n",
    "2. 使用where而不使用offset\n",
    "    - **id完整的情况**：eg：`limit 5,3 ==> where id > 5 limit 3;`\n",
    "    - PS：项目里面一般都是逻辑删除，id基本上算是比较完整的\n",
    "3. `覆盖索引+延迟关联`：通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据\n",
    "    - 使用场景：比如`主键为uuid`或`id不连续`（eg：部分数据物理删除了等等）\n",
    "\n",
    "说太空洞，演示下就清楚了：\n",
    "```sql\n",
    "-- 全表扫描\n",
    "explain\n",
    "    select *\n",
    "    from userinfo\n",
    "    limit 10000000,10; -- 3s\n",
    "\n",
    "-- 先range过滤了一部分\n",
    "explain\n",
    "    select *\n",
    "    from userinfo\n",
    "    where id > 10000000\n",
    "    limit 10; -- 20ms\n",
    "\n",
    "-- 内部查询使用了索引覆盖\n",
    "explain\n",
    "    select *\n",
    "    from userinfo\n",
    "             inner join (select id from userinfo limit 10000000,10) as tmp\n",
    "                        on userinfo.id = tmp.id; -- 2s\n",
    "```\n",
    "分析图示：\n",
    "![4.5.limit.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222125502664-1649291057.png)\n",
    "\n",
    "查询图示：\n",
    "![4.5.limit2.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222125811858-1243769812.png)\n",
    "\n",
    "### 扩展：索引误区和冗余索引\n",
    "\n",
    "#### 1.索引误区\n",
    "\n",
    "**很多人喜欢把where条件的常用列上都加上索引**，但是遗憾的事情是：**`独立的索引只能同时用上一个`**\n",
    "- **PS：在实际应用中往往选择`组合索引`**\n",
    "\n",
    "别不信，来验证一下就知道了：\n",
    "```sql\n",
    "-- id和email都是索引，但是只能使用一个索引（独立的索引只能同时用上一个）\n",
    "-- id的key-len=4（int4个字节）\n",
    "-- email的key-len=152（50*3(utf8下每个字符占3位)+2(varchar需要额外两个字节存放)==>152）\n",
    "\n",
    "-- 1.唯一索引和主键：优先使用主键\n",
    "explain\n",
    "    select * from users where id = 4 and email = 'xiaoming@qq.com';\n",
    "\n",
    "-- 2.组合索引和主键：优先使用主键\n",
    "explain\n",
    "    select * from users where id=4 and createtime='2019-02-16 17:10:29';\n",
    "\n",
    "-- 3.唯一索引和组合索引：优先使用唯一索引\n",
    "explain\n",
    "    select * from users where createtime='2019-02-16 17:10:29' and email='xiaoming@qq.com';\n",
    "\n",
    "-- 4.组合索引和一般索引：优先使用组合索引\n",
    "-- create index ix_users_datastatus on users(datastatus);\n",
    "-- create index ix_users_username_password on users(username,password);\n",
    "explain\n",
    "    select * from users where datastatus=1 and username='小明';\n",
    "-- 删除临时添加的索引\n",
    "-- drop index ix_users_datastatus on users;\n",
    "-- drop index ix_users_username_password on users;\n",
    "```\n",
    "图示：\n",
    "![5.2个索引.png](https://img2018.cnblogs.com/blog/1127869/201902/1127869-20190222133919898-1610968875.png)\n",
    "\n",
    "**PS：根据测试得知，一次只能使用1个索引。`索引优先级：主键 > 唯一 > 组合 > 普通`**\n",
    "\n",
    "#### 2.冗余索引\n",
    "\n",
    "举个标签表的例子：\n",
    "```sql\n",
    "create table tags\n",
    "(\n",
    "    id         int unsigned auto_increment primary key,\n",
    "    aid        int unsigned not null,\n",
    "    tag        varchar(25)  not null,\n",
    "    datastatus tinyint      not null default 0\n",
    ");\n",
    "insert into tags(aid,tag,datastatus) values (1,'Linux',1),(1,'MySQL',1),(1,'SQL',1),(2,'Linux',1),(2,'Python',1);\n",
    "\n",
    "select id, aid, tag, datastatus from tags;\n",
    "```\n",
    "输出：\n",
    "```\n",
    "+----+-----+--------+------------+\n",
    "| id | aid | tag    | datastatus |\n",
    "+----+-----+--------+------------+\n",
    "|  1 |   1 | MySQL  |          1 |\n",
    "|  2 |   1 | SQL    |          1 |\n",
    "|  3 |   2 | Linux  |          1 |\n",
    "|  4 |   2 | Python |          1 |\n",
    "+----+-----+--------+------------+\n",
    "```\n",
    "\n",
    "**实际应用中可能会`根据tag查找文章列表`，也可能`通过文章id查找对应的tag列表`**\n",
    "> 项目里面一般是这么建立索引（冗余索引）：index(文章id,tag),index(tag,文章id)，这样在上面两种情况下可以直接用到覆盖索引\n",
    "\n",
    "```sql\n",
    "create index ix_tags_aid_tag on tags(aid,tag);\n",
    "create index ix_tags_tag_aid on tags(tag,aid);\n",
    "\n",
    "select tag from tags where aid=1;\n",
    "select aid from tags where tag='Linux';\n",
    "```\n",
    "\n",
    "#### 3.修复碎片\n",
    "\n",
    "这边简单说下，下一章应该还会继续说运维相关的知识\n",
    "\n",
    "数据库表使用时间长了会出现碎片，可以定期修复一下（不影响数据）：**`optimize table users;`**\n",
    "> 修复表的数据以及索引碎片会把数据文件整理一下，这个过程相对耗费时间（数据量大的情况下）一般根据情况选择按周|月|年修复一下\n",
    "\n",
    "PS：可以配合`crontab`（定时任务）使用：\n",
    "- 使用命令：`crontab -e`：`***** 命令 [ > /dev/null 2>&1 ]`\n",
    "    - **`5个*的含义`**：`分`、`时`、`日`、`月`、`周`\n",
    "    - 从定向知识：\n",
    "        - `>> /xx/日志文件`：输出重定向到日记文件（不包含错误信息）\n",
    "        - `>> /xx/日志文件 2>&1`：输出信息包括错误信息\n",
    "        - `> /dev/null 2>&1`：出错信息重定向到垃圾桶（黑洞）\n",
    "    - 举几个栗子：\n",
    "        - `21*** xxx` ==> 每天 1:02 执行 xxx命令\n",
    "        - `5921*** xxx` ==> 每天 21::59 执行 xxx命令\n",
    "        - `*/*1*** xxx` ==> 每1小时 执行一次xxx命令\n",
    "            - 定时任务以`*/`开头\n",
    "\n",
    "**下期预估：SQL运维**\n",
    "\n",
    "课后拓展：\n",
    "```\n",
    "【推荐】一步步分析为什么B+树适合作为索引的结构\n",
    "https://blog.csdn.net/weixin_30531261/article/details/79312676\n",
    "\n",
    "善用mysql中的FROM_UNIXTIME()函数和UNIX_TIMESTAMP()函数\n",
    "https://www.cnblogs.com/haorenergou/p/7927591.html\n",
    "\n",
    "【推荐】MySQL crc32 & crc64函数 提高字符串查询效率\n",
    "https://www.jianshu.com/p/af6cc7b72dac\n",
    "\n",
    "MySQL优化之profile\n",
    "https://www.cnblogs.com/lizhanwu/p/4191765.html\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.6.SQL运维篇\n",
    "\n",
    "运维这块逆天只能说够用，并不能说擅长，所以这篇就当抛砖之用，欢迎补充和纠错\n",
    "\n",
    "PS：再说明下`CentOS优化策略`这部分的内容来源：首先这块逆天不是很擅长，所以主要是参考网上的DBA文章，之后请教了下运维相关的朋友，大家辩证看就行了，我只能保证90%的准确度\n",
    "\n",
    "### 1.6.1.概念\n",
    "\n",
    "#### 1.RAID系\n",
    "\n",
    "RAID：磁盘冗余队列\n",
    "> 把多个容量小的磁盘组成一组容量更大的磁盘，并提供数据冗余来保证数据完整性的技术\n",
    "\n",
    "`RAID0`：数据条带（好处：成本低，应用：数据备份）\n",
    "> 需要硬盘数>=2，数据没有冗余或修复功能，只是多个小容量变成大容量的功能\n",
    "\n",
    "RAID1：磁盘镜像（好处：数据安全、读很快）\n",
    "> 磁盘的数据镜像到另一个磁盘上，最大限度的保证系统的可靠性和可修复性\n",
    "\n",
    "**`RAID5`**：分布式奇偶校验磁盘阵列（好处：性价比高，缺点：两块磁盘失效则整个卷的数据都无法恢复，应用：从数据库）\n",
    "> 把数据分散到多个磁盘上，如果任何一个盘数据失效都可以从奇偶校验块中重建\n",
    "\n",
    "RAID10：分片镜像（优点：读写性能良好，相对RAID5重建更简单速度更快，缺点：贵）\n",
    "> 对磁盘先做RAID1之后对两组RAID1的磁盘再做RAID0\n",
    "\n",
    "| RAID级别    | 特点                 | 备份 | 盘数 | 读  | 写           |\n",
    "| ----------- | -------------------- | ---- | ---- | --- | ------------ |\n",
    "| **`RAID0`** | 便宜，读写快，不安全 | 没有 | N    | 快  | 快           |\n",
    "| `RAID1`     | 贵，高速读，最安全   | 有   | 2N   | 快  | 慢           |\n",
    "| **`RAID5`** | 性价比高，读快，安全 | 有   | N+1  | 快  | 取决于最慢盘 |\n",
    "| `RAID10`    | 贵，高速，安全       | 有   | 2N   | 快  | 快           |\n",
    "\n",
    "#### 2.SAN和NAS\n",
    "\n",
    "SAN：通过专用高速网将一个或多个网络存储设备和服务器连接起来的专用存储系统\n",
    "> 通过光纤连接到服务器，设备通过块接口访问，服务器可以将其当做硬盘使用\n",
    "\n",
    "**NAS**：连接在网络上, 具备资料存储功能的装置，以数据为中心，将存储设备与服务器彻底分离，集中管理数据，从而释放带宽、提高性能、降低总拥有成本、保护投资。其成本远远低于使用服务器存储，而效率却远远高于后者\n",
    "> 使用网络进行连接，通过基于文件协议（NFS、**SMB**）来访问\n",
    "\n",
    "**PS：网络存储一般都是用来搭建开发环境或者数据库备份**\n",
    "\n",
    "#### 3.QPS和TPS\n",
    "\n",
    "**`QPS`**（Queries Per Second）:**每秒钟处理的请求数**（一般都是查询，但DML、DDL也包括）\n",
    "> eg：`10ms`处理1个sql，1s处理100个sql，那么`QPS<=100`（`100ms`处理1个sql，QPS<=10）\n",
    "\n",
    "`TPS`（Transactions Per Second）:每秒钟系统能够处理的交易或事务的数量（`每秒事务数|消息数`）\n",
    "> 一个事务是指一个客户机向服务器发送请求然后服务器做出反应的过程。客户机在发送请求时开始计时，收到服务器响应后结束计时，以此来计算使用的时间和完成的事务个数\n",
    "\n",
    "**PS：QPS看的多些**\n",
    "\n",
    "### 1.6.2.常见问题\n",
    "\n",
    "1.**超高的CPU|内存使用率**：容易因CPU|内存资源耗尽而宕机\n",
    "> PS：如果是CPU密集型：需要更好的CPU；需要更大的并发量：需要更多的CPU（WEB项目）\n",
    "\n",
    "MySQL有同一数据中多次写操作合并为一次写操作\n",
    "\n",
    "2.**并发量大**：容易导致数据库连接数被占满\n",
    "> PS：MySQL的`max_connections`默认是100（根据硬件条件调整）\n",
    "\n",
    "3.**磁盘IO**：导致性能直线下降（`热点数据内存放不下时`）\n",
    "> 解决：定期整理磁盘碎片、`RAID增强传统硬盘`、`SSD`、`Fusion-io`（PCIe）、`网络存储NAS or ASN`\n",
    "\n",
    "PS：SSD应用于存在`大量随机IO`或解决`单线程IO瓶颈`的场景\n",
    "\n",
    "4.**网卡流量**（网络）：容易出现无法连接数据库的现象\n",
    "> 解决：\n",
    "> 1. 减少从服务器的数量\n",
    "> 2. 分级缓存（防止同一时间缓存的大量失效）\n",
    "> 3. 避免使用`select *`进行查询（减少传输过程中的无用字节）\n",
    "> 4. 分离业务网络和服务器网络\n",
    "\n",
    "5.**大表**定义：单表数据量超过千万行 or 表数据文件超过10G\n",
    "> 问题：大表更容易出现慢查询、DDL也很慢也容易导致其他问题\n",
    "> 解决：分库分表（拆分为多个小表）\n",
    "> PS：分库分表前可以对大表的历史数据进行归档（冷热数据隔离）【核心：归档时间点的选择】\n",
    "\n",
    "**DDL影响的补充说明：**\n",
    "- `建索引很慢`，而且会引起长时间的主从延迟\n",
    "- `修改表结构需要长时间锁表`\n",
    "    - 引起长时间的主从延迟\n",
    "    - 影响正常的数据操作\n",
    "\n",
    "**分库分表容易出现的问题：**\n",
    "1. 分表主键的选择\n",
    "    - 不能保证id是全局唯一，这时候可以使用诸如`雪花算法`来解决\n",
    "2. 跨库跨表的join问题\n",
    "3. 事物问题（分布式事物诞生了）\n",
    "\n",
    "PS：不太影响的案例：**日志表**（`insert`和`select`很多，很少delete和update）\n",
    "\n",
    "6.**大事务**定义：运行时间较长，操作数据比较多的事物\n",
    "> 问题：\n",
    "> 1. 锁定太多的数据，造成大量的阻塞和锁超时\n",
    "> 2. 回滚需要的时间很长（又得锁一段时间了）\n",
    "> 3. 执行时间长，容易导致主从的延迟\n",
    "> 解决：\n",
    "> 1. 避免一次处理大量数据（分批处理）\n",
    "> 2. 去除在事物中不必要的select语句（一般都是事物中使用过多查询导致的）\n",
    ">     - PS：select完全可以在事物外查询，事物专注于写\n",
    "\n",
    "**SQL标准中定义的4种隔离级别：**\n",
    "1. _未提交读（`read uncommited`）_\n",
    "2. **已提交读**（`read commited`）\n",
    "    - 不可重复读\n",
    "3. **可重复读**（`repeatable read`）\n",
    "    - `innodb的默认隔离级别`\n",
    "4. _可串行化（`serializable`）_\n",
    "5. **PS：隔离性低到高，`并发性高到低`**\n",
    "\n",
    "**PS：查看事物隔离级别-`show variables like '%iso%';`，设置会话的隔离级别：`set session tx_isolation='read-committed'`**\n",
    "\n",
    "### 扩展：CentOS优化策略（MySQL服务器）\n",
    "\n",
    "#### 1.内核相关（`/etc/sysctl.conf`）\n",
    "\n",
    "**查看默认值：`sysctl -a`**\n",
    "\n",
    "**tcp相关设置：**\n",
    "```shell\n",
    "# 三次握手listen的最大限制\n",
    "net.core.somaxconn = 65535 # 默认是128\n",
    "\n",
    "# 当网络接受速率大于内核处理速率时，允许发送到队列中的包数\n",
    "net.core.netdev_max_backlog = 65535 # 默认是1000\n",
    "\n",
    "# Linux队列的最大半连接数（超过则丢包）\n",
    "net.ipv4.tcp_max_syn_backlog = 65535 # 默认是128（不适合Web服务器）\n",
    "```\n",
    "PS：这边只是一个参考，自己可以根据环境适当降低（最大端口数一般都是65535）\n",
    "> 注意：如果是Web服务器，`net.ipv4.tcp_max_syn_backlog`**不宜过大**（容易有synflood攻击的安全问题），`net.ipv4.tcp_tw_recycle`和`net.ipv4.tcp_tw_reuse`**不建议开启**\n",
    "\n",
    "**加快tcp链接回收的几个参数：**\n",
    "```shell\n",
    "# TCP等待时间，加快tcp链接回收\n",
    "net.ipv4.tcp_fin_timeout = 10 # 默认60\n",
    "\n",
    "# 把发起关闭，但关闭没完成的TCP关闭掉\n",
    "net.ipv4.tcp_tw_recycle = 1   # 默认0（不适合Web服务器）\n",
    "\n",
    "# 允许待关闭的socket建立新的tcp\n",
    "net.ipv4.tcp_tw_reuse = 1     # 默认0（不适合Web服务器）\n",
    "```\n",
    "PS：`net.ipv4.tcp_tw_reuse`扩展说明：主动调用closed的一方才会在接收到对端的ACK后进入time_wait状态\n",
    "> 参考文章：`https://blog.csdn.net/weixin_41966991/article/details/81264095`\n",
    "\n",
    "**缓存区大小的最大值和默认值：**\n",
    "```shell\n",
    "net.core.wmem_default = 87380 # 默认212992\n",
    "net.core.wmem_max = 16777216  # 默认212992\n",
    "net.core.rmem_default = 87380 # 默认212992\n",
    "net.core.rmem_max = 16777216  # 默认212992\n",
    "```\n",
    "PS：每个`socket`都会有一个`rmem_default`大小的缓存空间（如果设置了`setsockopt`则就是多少，最大不超过`rmem_max`）\n",
    "\n",
    "**减少失效连接所占用的系统资源**：\n",
    "```shell\n",
    "# 对于tcp失效链接占用系统资源的优化，加快资源回收效率\n",
    "# 链接有效时间（单位s）\n",
    "net.ipv4.tcp_keepalive_time = 120 # 默认7200\n",
    "\n",
    "# tcp未获得相应时重发间隔（单位s）\n",
    "net.ipv4.tcp_keepalive_intvl = 30 # 默认75\n",
    "\n",
    "# 重发数量（单位s）\n",
    "net.ipv4.tcp_keepalive_probes = 3 # 默认9\n",
    "```\n",
    "\n",
    "**内存相关参数：**\n",
    "```shell\n",
    "# 共享单个共享内存下的最大值\n",
    "kernel.shmmax = 4294967295   # 最大为物理内存-1byte\n",
    "\n",
    "# 除非虚拟内存全部占满，否则不使用交换分区（为了性能）\n",
    "# （free -m ==> Swap）\n",
    "vm.swappiness = 0            # 默认30\n",
    "```\n",
    "**PS：`kernel.shmmax`设置的足够大，一般就是为了容纳整个innodb的缓冲池**\n",
    "> eg：`4G = 4*1024 M = 4*1024*1024 KB = 4*1024*1024*1024 byte = 4294967296 - 1 = 4294967295`\n",
    "> PS：`unsigned int` => `[0, 2^32)` => `[0,4294967296)` => `[0,4294967295]` **巧不，一样的值**\n",
    "\n",
    "#### 2.资源限制（`/etc/security/limit.conf`）\n",
    "\n",
    "**打开文件数的限制**（追加到配置后即可）\n",
    "```shell\n",
    "# [*|%] [soft|hard] [type_item] [value]\n",
    "* soft nofile 65536\n",
    "* hard nofile 65535\n",
    "```\n",
    "默认值：**`ulimit -a`**\n",
    "```shell\n",
    "core file size          (blocks, -c) 0\n",
    "data seg size           (kbytes, -d) unlimited\n",
    "scheduling priority             (-e) 0\n",
    "file size               (blocks, -f) unlimited\n",
    "pending signals                 (-i) 3548\n",
    "max locked memory       (kbytes, -l) 64\n",
    "max memory size         (kbytes, -m) unlimited\n",
    "open files                      (-n) 1024  《《看这  \n",
    "pipe size            (512 bytes, -p) 8\n",
    "POSIX message queues     (bytes, -q) 819200\n",
    "real-time priority              (-r) 0\n",
    "stack size              (kbytes, -s) 8192\n",
    "cpu time               (seconds, -t) unlimited\n",
    "max user processes              (-u) 3548\n",
    "virtual memory          (kbytes, -v) unlimited\n",
    "file locks                      (-x) unlimited\n",
    "```\n",
    "\n",
    "PS：一般来说是够用了，但是一个遇到大型数据库可能就不够看了（多表多库配置高）\n",
    "> `*：所有用户有效、soft：当前系统生效、hard：系统中所能设置的最大值、nofile：所限制的资源是打开文件的最大数、65536：数值`【**重启才生效**】\n",
    "\n",
    "#### 3.磁盘调度策略（`/sys/block/devname/queue/scheduler`）\n",
    "\n",
    "现在默认策略就是`deadline`，所以不用优化了【对数据库支持很不错】\n",
    "\n",
    "PS：通过`cat /sys/block/sda/queue/scheduler`查看（`[这个就是设置的值]`）\n",
    "```shell\n",
    "noop [deadline] cfq \n",
    "```\n",
    "\n",
    "如果不是**可以通过：`echo deadline > /sys/block/sda/queue/scheduler`来设置**\n",
    "> `cfq`：会在队列中插入一些不必要的请求，会导致相应时间加长，一般桌面系统用的比较多\n",
    "\n",
    "> `noop`：实现了一个FIFO队列，像电梯工作一样对IO请求进行组织，当有一个新请求到来时会合并到最近请求之后，以此保证请求同一介质（倾向于饿死读而利于写）一般`闪存设备`、`RAM`、`嵌入式系统`用的比较多\n",
    "\n",
    "> `deadline`：确保了在一个截止时间内去服务请求（可调整）默认读期限短于写期限（防止写操作因为不能被读而出现饿死的现象）\n",
    "\n",
    "#### 4.文件系统\n",
    "\n",
    "Win：`NTFS`，Linux：EXT3|4、`XFS`\n",
    "\n",
    "Linux现在基本上都是选择`XFS`，如果是`EXT3`、`EXT4`还需要设置一下：`/etc/fstab`（**慎重**）\n",
    "```shell\n",
    "/dev/sda1/ext4 noatime,nodiratime,data=writeback 1 1\n",
    "```\n",
    "PS：`noatime`表示不记录访问时间，`nodiratime`不记录目录的访问时间（可以减少一些写的操作）\n",
    "> 不同的日志策略：`data=[wtiteback|ordered|journal]`\n",
    "> 1. writeback：只有原数据写入日志，原数据写入和数据写入并不是同步的（最快）PS：Innodb有自己的事务日志，所以是最好的选择\n",
    "> 2. ordered：只会记录原数据，但提供了一些一致性的保证，在写原数据之前会先写数据，使他们保持一致（比writeback慢但更安全）\n",
    "> 3. journal：提供了原子日志的一种行为，在数据写入到最终位置之前，将记录到日志中（最慢，对Innodb来说是没有必要）\n",
    "\n",
    "课后拓展：\n",
    "```\n",
    "TPS、并发用户数、吞吐量关系\n",
    "https://www.cnblogs.com/zhengah/p/4532156.html\n",
    "\n",
    "针对Mysql所在linux服务器的系统优化参数\n",
    "https://blog.csdn.net/qq_40999403/article/details/80666102\n",
    "\n",
    "网络优化之net.ipv4.tcp_tw_recycle参数\n",
    "https://blog.csdn.net/chengm8/article/details/51668992\n",
    "\n",
    "linux socket 缓存: core rmem_default rmem_max\n",
    "https://blog.csdn.net/penzchan/article/details/41682411\n",
    "\n",
    "Linux上的free命令详解、swap机制\n",
    "http://www.cnblogs.com/xiaojianblogs/p/6254535.html\n",
    "\n",
    "磁盘IO过高时的处理办法\n",
    "https://www.cnblogs.com/wjoyxt/p/4808024.html\n",
    "\n",
    "文件系统对性能的影响\n",
    "https://blog.csdn.net/qq_30353203/article/details/78197870\n",
    "```\n",
    "\n",
    "### 1.6.3.MySQL配置参数\n",
    "\n",
    "**建议：优先从数据库设计和SQL优化着手，然后才是配置优化和存储引擎的选择，最后才是硬件提升**\n",
    "> 设计案例：`列太多`不行，`关联太多`也不行（10个以内），不恰当的`分区表`，使用了外键\n",
    "\n",
    "分区表：一个服务器下，逻辑上还是一个表，物理存储上分成了多个表（<a href=\"https://www.cnblogs.com/dunitian/p/6078512.html\" target=\"_blank\">类似于SQLServer的水平分库</a>）\n",
    "> PS：分库分表：物理和逻辑上都拆分成多个表了\n",
    "\n",
    "之前讲环境的时候简单说了下最基础的\n",
    "```shell\n",
    "[mysqld]\n",
    "# 独立表空间: 每一个表都有一个.frm表描述文件，还有一个.ibd文件\n",
    "innodb_file_per_table=on\n",
    "# 不对连接进行DNS解析(省时)\n",
    "skip_name_resolve=on\n",
    "# 配置sql_mode\n",
    "sql_mode='strict_trans_tables'\n",
    "```\n",
    "\n",
    "然后说`SQL_Mode`的时候简单说了下`全局参数`和`会话参数`的设置方法：<a href=\"https://www.cnblogs.com/dotnetcrazy/p/10374091.html\" target=\"_blank\">MySQL的SQL_Mode修改小计</a>\n",
    "- 全局参数设置：`set global 参数名=参数值;`\n",
    "    - 只对新会话有效，重启后失效\n",
    "- 会话参数设置：`set [session] 参数名=参数值`\n",
    "    - 只对当前会话有效，其他会话不影响\n",
    "\n",
    "这边继续说下其他几个**影响较大**的配置参数：（**对于开发人员来说，简单了解即可，这个是DBA的事情了**）\n",
    "\n",
    "#### 1.安全相关配置\n",
    "\n",
    "- `expire_logs_days`：自动清理binlog\n",
    "    - PS：一般最少保存7天（具体根据业务来）\n",
    "- **`max_allowed_packet`：配置MySQL接收包的大小**\n",
    "    - PS：默认太小。如果配置了主从，需要配置成一样大（防止丢包）\n",
    "- **`skip_name_resolve`：禁用DNS查找**（这个我们之前说过了，主要是提速）\n",
    "    - **PS：如果启用了，那么进行用户授权时，只能通过`ip`或者`ip段`或者`本机host出现过的域名`进行授权**\n",
    "        - 用`*`的是没影响的\n",
    "- `sysdata_is_now`：**保证sysdate()返回确定性日期**\n",
    "    - PS：如果主从使用了binlog的`statement`模式，sysdata的结果会不一样，最后导致数据不一致\n",
    "        - 类似的问题还有很多，eg：获取最后一次id的时候（`last_insert_id()`）\n",
    "        - 扩：现在MySQL有了`Mixed`模式\n",
    "- `read_only`：一般用户只能读数据，只有root用户可以写：\n",
    "    - **PS：推荐在从库中开启，这样就只接受从主库中的写操作，其它只读**\n",
    "    - 从库授权的时候不要授予超级管理员的权限，不然这个参数相当于废了\n",
    "- `skip_slave_start`：**禁用从库(`Slave`)自动恢复**\n",
    "    - MySQL在重启后会自动启用复制，这个可以禁止\n",
    "    - PS：不安全的崩溃后，复制过去的数据可能也是不安全的（手动启动更合适）\n",
    "- **`sql_mode`：设置MySQL的SQL模式**（这个上次说过，默认是宽松的检测，这边再补充几个）\n",
    "    - **`strict_trans_tables`：对所有支持事物类型的表做严格约束**：\n",
    "        - **最常见**，主要对事物型的存储引擎生效，其他的没效果\n",
    "        - PS：如果插入数据不符合规范，则中断当前操作\n",
    "    - **`no_engine_subtitution`：建表的时候指定不可用存储引擎会报错**\n",
    "    - **`only_full_group_by`**：检验`group by`语句的合法性\n",
    "        - 要求在在分组查询语句中，把所有没有使用聚合函数的列，列出来\n",
    "        - eg：`select count(url),name from file_records group by url;`\n",
    "            - 使用了name字段，name不是聚合函数，那必须在group by中写一下\n",
    "    - **`ansi_quotes`**：不允许使用双引号来包含字符串\n",
    "        - PS：防止数据库迁移的时候出错\n",
    "    - **PS：生存环境下最好不要修改，容易报错对业务产生影响（严格变宽松没事）**\n",
    "\n",
    "**PS：一般`SQL_Mode`是测试环境相对严格（`strict_trans_tables,only_full_group_by,no_engine_subtitution,ansi_quotes`），线上相对宽松（`strict_trans_tables`）**\n",
    "\n",
    "**补充说下`sysdate()`和`now()`的区别:**（看个案例就懂了）\n",
    "> PS：对于一个语句中调用多个函数中`now()`返回的值是**执行时刻的时间**，而`sysdate()`返回的是**调用该函数的时间**\n",
    "\n",
    "```sql\n",
    "MariaDB [(none)]> select sysdate(),sleep(2),sysdate();\n",
    "+---------------------+----------+---------------------+\n",
    "| sysdate()        | sleep(2) | sysdate()        |\n",
    "+---------------------+----------+---------------------+\n",
    "| 2019-03-28 09:09:29 |     0 | 2019-03-28 09:09:31  |\n",
    "+---------------------+----------+---------------------+\n",
    "1 row in set (2.001 sec)\n",
    "\n",
    "MariaDB [(none)]> select now(),sleep(2),now();\n",
    "+---------------------+----------+---------------------+\n",
    "| now()           | sleep(2) | now()          |\n",
    "+---------------------+----------+---------------------+\n",
    "| 2019-03-28 09:09:33 |     0 | 2019-03-28 09:09:33  |\n",
    "+---------------------+----------+---------------------+\n",
    "1 row in set (2.000 sec)\n",
    "```\n",
    "\n",
    "#### 2.内存相关\n",
    "\n",
    "- **`sort_buffer_size`：每个会话使用的排序缓冲区大小**\n",
    "    - PS：每个连接都分配这么多eg：1M，100个连接==>100M（默认是全部）\n",
    "- **`join_buffer_size`：每个会话使用的表连接缓冲区大小**\n",
    "    - PS：给每个join的表都分配这么大，eg：1M，join了10个表==>10M\n",
    "- **`binlog_cache_size`：每个会话未提交事物的缓冲区大小**\n",
    "- **`read_rnd_buffer_size`：设置索引缓冲区大小**\n",
    "- **`read_buffer_size`：对MyISAM全表扫描时缓冲池大小**（一般都是4k的倍数）\n",
    "    - PS：对临时表操作的时候可能会用到\n",
    "\n",
    "**`read_buffer_size`的扩充说明：**\n",
    "> 现在基本上都是Innodb存储引擎了，大部分的MyISAM的配置就不用管了，但是这个还是需要配置下的\n",
    "\n",
    "引入下**临时表知识扩展**：\n",
    "1. 系统使用临时表：\n",
    "   - 不超过16M：系统会使用`Memory`表\n",
    "   - 超过限制：使用`MyISAM`表\n",
    "2. 自己建的临时表：（可以使用任意存储引擎）\n",
    "   - `create temporary table tb_name(列名 类型 类型修饰符,...)`\n",
    "\n",
    "**PS：现在知道为啥配置`read_buffer_size`了吧（系统使用临时表的时候，可能会使用`MyISAM`）**\n",
    "\n",
    "#### 3.IO相关参数\n",
    "\n",
    "**主要看看`Innodb`的`IO`相关配置**\n",
    "\n",
    "事物日志：（总大小:`Innodb_log_file_size * Innodb_log_files_in_group`）\n",
    "- 事物日志大小：`Innodb_log_file_size`\n",
    "- 事物日志个数：`Innodb_log_files_in_group`\n",
    "\n",
    "日志缓冲区大小：`Innodb_log_buffer_size`\n",
    "> 一般日志先写到缓冲区中，再刷新到磁盘（一般32M~128M就够了）\n",
    "\n",
    "知识扩展：`redo Log`内存中缓冲区的大小：(字节为单位)\n",
    "\n",
    "- `show variables like 'innodb_log_buffer_size';`\n",
    "    - PS：以字节为单位，每隔1s就会把数据存储到磁盘上\n",
    "- `show variables like 'innodb_log_files_in_group';`\n",
    "    - PS：有几个就产生几个`ib_logfile`文件（默认是2）\n",
    "\n",
    "---\n",
    "\n",
    "**日志刷新频率：`Innodb_flush_log_at_trx_commit`**\n",
    "- 0：每秒进行一次日志写入缓存，并刷新日志到磁盘（最多丢失1s）\n",
    "- 1：每次交执事物就把日志写入缓存，并刷新日志到磁盘（**默认**）\n",
    "- 2：每次事物提交就把日志写入缓存，每秒刷新日志到磁盘（**推荐**）\n",
    "\n",
    "**刷新方式：`Innodb_flush_method=O_DIRECT`**\n",
    "> 关闭操作系统缓存（避免了操作系统和Innodb双重缓存）\n",
    "\n",
    "**如何使用表空间：`Innodb_file_per_table=1`**\n",
    "> 为每个innodb建立一个单独的表空间（这个基本上已经成为通用配置了）\n",
    "\n",
    "是否使用双写缓存：`Innodb_doublewrite=1`（避免发生页数据损坏）\n",
    "- 默认是开启的，如果出现写瓶颈或者不在意一些数据丢失可以不开启（开启后性能↑↑）\n",
    "- 查看是否开启：`show variables like '%double%';`\n",
    "\n",
    "**设置innodb缓冲池大小：`innodb_buffer_pool_size`**\n",
    "> 如果都是innodb存储引擎，这个参数的设置可以这样来算：（**一般都是内存的`75%`**）\n",
    "> 查看命令：`show global variables like 'innodb_buffer_pool_size';`\n",
    ">**PS：缓存数据和索引（直接决定了innodb性能）** 课后拓展：<https://www.cnblogs.com/wanbin/p/9530833.html>\n",
    "\n",
    "**innodb缓存池实例的个数：`innodb_buffer_pool_instances`**\n",
    "> PS：主要目的为了减少资源锁增加并发。`每个实例的大小=总大小/实例的个数`\n",
    "> 一般来说，每个实例大小不能小于1G，而且个数不超过8个\n",
    "\n",
    "#### 4.其他服务器参数\n",
    "\n",
    "- **`sync_binlog`：控制MySQL如何像磁盘中刷新binlog**\n",
    "    - 默认是0，MySQL不会主动把缓存存储到磁盘，而是靠操作系统\n",
    "    - PS：为了数据安全，建议主库设置为1（效率也容易降低）\n",
    "        - 还是那句话：一般不去管，具体看业务\n",
    "- 控制内存临时表大小：`tmp_table_size` and `max_heap_table_size`\n",
    "    - PS：建议保持两个参数一致\n",
    "- **`max_connections`：设置最大连接数**\n",
    "    - 默认是100，可以根据环境调节，**太大可能会导致内存溢出**\n",
    "- **`Sleep`等待时间**：一般设置为相同值（通过连接参数区分是否是交互连接）\n",
    "    - `interactive_timeout`：设置交互连接的timeout时间\n",
    "    - `wait_timeout`：设置非交互连接的timeout时间\n",
    "\n",
    "#### 扩展工具：`pt-config-diff`\n",
    "\n",
    "使用参考：`pt-config-diff u=root,p=pass,h=localhost /etc/my.conf`\n",
    "\n",
    "eg：比较配置文件和服务器配置\n",
    "```\n",
    "pt-config-diff /etc/my.cnf  h=localhost --user=root --password=pass\n",
    "3 config differences\n",
    "Variable                  /etc/my.cnf mariadb2\n",
    "========================= =========== ========\n",
    "max_connect_errors        2           100\n",
    "rpl_semi_sync_master_e... 1           OFF\n",
    "server_id                 101         102\n",
    "```\n",
    "\n",
    "课后拓展：<https://www.cndba.cn/leo1990/article/2789>\n",
    "\n",
    "---\n",
    "\n",
    "### 扩展：常见存储引擎\n",
    "\n",
    "常见存储引擎：\n",
    "1. MyISAM：不支持事物，表级锁\n",
    "    - 索引存储在内存中，数据放入磁盘\n",
    "    - 文件后缀：`frm、MYD、MYI`\n",
    "2. **`Innodb`**：事物级存储引擎，支持行级锁和事物ACID特性\n",
    "    - 同时在内存中缓存索引和数据\n",
    "    - 文件后缀：`frm、ibd`\n",
    "3. `Memory`：表结构保存在磁盘文件中，表内容存储在内存中\n",
    "    - Hash索引、B-Tree索引\n",
    "    - PS：容易丢失数据（重启后数据丢失，表结构依旧存在）\n",
    "4. `CSV`：一般都是作为中间表\n",
    "    - 以文本方式存储在文件中，**不适合大表**\n",
    "    - frm（表结构）、CSV（表内容）、CSM（元数据，eg：表状态、数据量）\n",
    "    - PS：不支持索引（engine=csv），所有列不能为Null\n",
    "    - 详细可以查看上次写的文章：<a href=\"https://www.cnblogs.com/dotnetcrazy/p/10481483.html\" target=\"_blank\">小计：协同办公衍生出的需求</a>\n",
    "5. Archive：数据归档（压缩）\n",
    "    - 文件：`.frm`(存储表结构)、`.arz`(存储数据)\n",
    "    - 只支持`insert`和`select`操作\n",
    "    - 只允许在自增ID列上加上索引\n",
    "    - `适合场景：日志类`（省空间）\n",
    "6. _Federated_：建立远程连接表（性能不怎样，默认禁止）\n",
    "    - 本地不存储数据（数据全部在远程服务器上）\n",
    "    - 本地需要保存表结构和远程服务器的连接信息\n",
    "    - PS：类似于SQLServer的链接服务器\n",
    "\n",
    "**逆天点评：除非你有100%的理由，否则全选`innodb`，特别不建议混合使用**\n",
    "\n",
    "#### Memory存储引擎\n",
    "\n",
    "Memory存储引擎：\n",
    "1. 支持`Hash`和`BTree`两种索引\n",
    "    - Hash索引：等值查找（默认）\n",
    "    - Btree索引：范围查找\n",
    "        - `create index ix_name using btree on tb_name(字段,...)`\n",
    "    - PS：不同场景下的不同选择，性能差异很大\n",
    "2. 所有字段类型都等同于**固定长度**，且不支持`Text`和`Blog`等大字段类型\n",
    "    - eg：`varchar(100)`==等价于==> `char(100) `\n",
    "3. 存储引擎使用表级锁\n",
    "    - PS：性能不见得比innodb好\n",
    "4. 大小由`max_heap_table_size`决定（默认16M）\n",
    "    - PS：如果想存大点，就得改参数（对已经存在的表不生效，需要重建才行）\n",
    "5. 常用场景（`数据易丢失，要保证数据可再生`）\n",
    "   - **缓存周期性聚合数据的结果**\n",
    "   - 用于查找或者映射的表（eg：邮编和地区的对应表）\n",
    "   - 保存数据分析中产生的**中间表**\n",
    "\n",
    "**PS：现在基本上都是redis了，如果不使用redis的小项目可以考虑（eg：官网、博客...）**\n",
    "\n",
    "---\n",
    "\n",
    "文章拓展：\n",
    "```\n",
    "OLAP、OLTP的介绍和比较\n",
    "https://www.cnblogs.com/hhandbibi/p/7118740.html\n",
    "\n",
    "now()与sysdate()\n",
    "http://blog.itpub.net/22664653/viewspace-752576/\n",
    "https://stackoverflow.com/questions/24137752/difference-between-now-sysdate-current-date-in-mysql\n",
    "\n",
    "binlog三种模式的区别（row，statement，mixed）\n",
    "https://blog.csdn.net/keda8997110/article/details/50895171/\n",
    "\n",
    "MySQL-重做日志 redo log -原理\n",
    "https://www.cnblogs.com/cuisi/p/6525077.html\n",
    "\n",
    "详细分析MySQL事务日志(redo log和undo log)\n",
    "https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html\n",
    "\n",
    "innodb_flush_method的性能差异与File I/O\n",
    "https://blog.csdn.net/melody_mr/article/details/48626685\n",
    "\n",
    "InnoDB关键特性之double write\n",
    "https://www.cnblogs.com/geaozhang/p/7241744.html\n",
    "\n",
    "```\n",
    "\n",
    "---\n",
    "\n",
    "### 存储引擎的扩展\n",
    "\n",
    "#### 1.简单回顾\n",
    "\n",
    "上节在最后的时候说了下存储引擎，这边简单回顾下：\n",
    "\n",
    "| 存储引擎      | 是否支持事物 | 文字说明                             |\n",
    "| ------------- | ------------ | ------------------------------------ |\n",
    "| `MyISAM`      | 不支持       | MySQL5.6以前的默认存储引擎           |\n",
    "| `CSV`         | 不支持       | 用CSV格式来存储数据（一般当中间表）  |\n",
    "| **`Archive`** | 不支持       | 只能查询和添加数据（一般记录日志用） |\n",
    "| `Memory`      | 不支持       | 数据只存储在内存中（容易丢失）       |\n",
    "| **`innodb`**  | **支持**（行级锁）    | 现在基本上都使用这个                 |\n",
    "| `NDB`         | **支持**（行级锁）     | MySQL集群才使用（内存型，数据会持久化一份）            |\n",
    "\n",
    "补充说明：\n",
    "\n",
    "1. `Archive`存储引擎的数据会用`zlib`来**压缩**，而且**只支持在自增ID上添加索引**\n",
    "2. `NDB`存储引擎的数据存储在磁盘中（热数据存储在内存中），支持Ttree索引和集群\n",
    "    - 场景：数据需要完全同步（这些后面会继续说的）\n",
    "\n",
    "#### 2.常见场景\n",
    "\n",
    "提一个场景：**`innodb`表无法在线修改表结构的时候怎么解决？**\n",
    "\n",
    "先看下`Innodb不支持在线修改表结构`都有哪些情况：（主要从性能方面考虑）\n",
    "\n",
    "1. 第一次创建`全文索引`和添加`空间索引`（`MySQL5.6`以前版本不支持）\n",
    "    - **全文索引**：`create fulltext index name on table(列,...);`\n",
    "    - 空间索引：`alter table geom add spatial index(g);`\n",
    "2. 删除主键或者添加自增列\n",
    "    - PS：innodb存储就是按照主键进行顺序存储的（这时候需要重新排序）\n",
    "    - 删除主键：`alter table 表名 drop primary key`\n",
    "    - 加自增列：`alter table 表名 add column id int auto_increment primary key`\n",
    "3. 修改列类型、修改表字符集\n",
    "    - 修改列类型：`alter table 表名 modify 列名 类型 类型修饰符`\n",
    "    - 修改字符集：`alter table 表名 character set=utf8mb4`\n",
    "\n",
    "**PS：DDL不能并发执行**（表级锁）**长时间的DDL操作会导致主从不一致**\n",
    "> DDL没法进行资源限制，表数据多了容易占用大量存储IO空间（空间不够就容易执行失败）\n",
    "\n",
    "#### 3.解决方案\n",
    "\n",
    "**安装：`yum install percona-toolkit` or `apt-get install percona-toolkit`**\n",
    "> PS：离线包：`https://www.percona.com/downloads/percona-toolkit/LATEST/`\n",
    "\n",
    "命令：`pt-online-schema-change 选项 D=数据库,t=表名,u=用户名,p=密码`\n",
    "> **原理：先创建一个类型修改完的表，然后把旧表数据copy过去，然后删除旧表并重命名新表**\n",
    "\n",
    "\n",
    "**查看帮助文档：`pt-online-schema-change --help | more`**\n",
    "> 官方文档：<https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html>\n",
    "\n",
    "**PS：一般就`--alter`和`--charset`用的比较多**（`--execute`代表执行）\n",
    "\n",
    "**常用：`pt-online-schema-change --alter \"DDL语句\" --execute D=数据库,t=表名,u=用户名,p=密码`**\n",
    "> eg：添加新列：`pt-online-schema-change --alter \"add 列名 类型\" --execute D=数据库,t=表名,u=用户名,p=密码`\n",
    "\n",
    "**知识回顾**：\n",
    "\n",
    "- 添加字段：add\n",
    "    - `alter table tb_name add 列名 数据类型 修饰符 [first | after 列名];`\n",
    "    - **PS：SQLServer没有`[first | after 列名]`**\n",
    "- 修改字段：alter、change、modify\n",
    "    - 修改字段名：`alter table tb_name change 旧列名 新列名 类型 类型修饰符`\n",
    "    - 修改字段类型：`alter table tb_name modify 列名 类型 类型修饰符`\n",
    "    - 添加默认值：`alter table tb_name alter 列名 set default df_value`\n",
    "- 删除字段：drop\n",
    "    - `alter table tb_name drop 字段名`\n",
    "\n",
    "---\n",
    "\n",
    "#### 4.InnoDB专栏\n",
    "\n",
    "写在前面的概念：**排它锁（别名：独占锁、写锁）、共享锁（别名：读锁）**\n",
    "\n",
    "##### 4.1.innoDB是如何实现事物的？\n",
    "> 事物4大特性：A（原子性）C（一致性）I（隔离性）D（持久性）\n",
    "\n",
    "innodb事务日志主要就是`redo log`（重做日志）和`undo log`（回滚日志）\n",
    "\n",
    "| 事物特性  | innodb实现方式                                      |\n",
    "| --------- | --------------------------------------------------- |\n",
    "| 原子性(A) | 回滚日志(`undo log`)：用于记录数据**修改前**的状态  |\n",
    "| 一致性(C) | 重做日志(`redo log`)：用于记录数据**修改后**的状态  |\n",
    "| 隔离性(I) | 锁(`lock`)：用于资源隔离（**共享锁** + **排他锁**） |\n",
    "| 持久性(D) | 重做日志(`redo log`) + 回滚日志(`undo log`)         |\n",
    "\n",
    "我画个转账案例：\n",
    "\n",
    "![05/05/1.innodb事物实现.png](https://img2018.cnblogs.com/blog/1127869/201905/1127869-20190505224339502-228772932.png)\n",
    "\n",
    "##### 4.2.innodb`读`操作是否会阻塞`写`操作？\n",
    "\n",
    "一般情况下：`查询`需要对资源添加**共享锁**（读锁） | `修改`需要对资源添加**排它锁**（写锁）\n",
    "\n",
    "| 是否兼容 | `写锁` | `读锁`   |\n",
    "| -------- | ------ | -------- |\n",
    "| `写锁`   | 不兼容 | 不兼容   |\n",
    "| `读锁`   | 不兼容 | **兼容** |\n",
    "\n",
    "PS：共享锁和共享锁之间是可以共存的（读的多并发）**理论上讲读操作和写操作应该相互阻塞**\n",
    "\n",
    "而`innodb`看起来却仿佛打破了这个常规，看个案例：\n",
    "\n",
    "1.启动一个事物，但是不提交\n",
    "\n",
    "![03/03/1.1.事物不提交.png](https://img2018.cnblogs.com/blog/1127869/201903/1127869-20190306095710937-1174442755.png)\n",
    "\n",
    "2.在另一个连接中查询\n",
    "\n",
    "![03/03/1.2.另一个连接查询的结果是undolog中的记录.png](https://img2018.cnblogs.com/blog/1127869/201903/1127869-20190306095741612-1352578942.png)\n",
    "\n",
    "PS：理论上独占锁没提交时是不能读操作的，**但`innodb`做了优化，会查询`undo log`（未修改前的数据）中的记录**来提高并发性\n",
    "\n",
    "3.提交事物后再查询，这时候就看到更新后的数据了\n",
    "\n",
    "![03/03/1.3.更新了.png](https://img2018.cnblogs.com/blog/1127869/201903/1127869-20190306095826759-1724313709.png)\n",
    "\n",
    "**PS：这个就是innodb的`MVCC`（多版本并发控制）**\n",
    "\n",
    "---\n",
    "\n",
    "知识拓展：\n",
    "\n",
    "```\n",
    "【推荐】Mysql的InnoDB事务多版本并发控制如何实现（MVCC）\n",
    "\n",
    "https://www.cnblogs.com/aspirant/p/6920987.html\n",
    "\n",
    "https://blog.csdn.net/u013007900/article/details/78641913\n",
    "\n",
    "https://www.cnblogs.com/dongqingswt/p/3460440.html\n",
    "\n",
    "https://www.jianshu.com/p/a3d49f7507ff\n",
    "\n",
    "https://www.jianshu.com/p/a03e15e82121\n",
    "\n",
    "https://www.jianshu.com/p/5a9c1e487ddd\n",
    "\n",
    "基于mysql全文索引的深入理解\n",
    "https://www.cnblogs.com/dreamworlds/p/5462018.html\n",
    "\n",
    "【推荐】MySQL中的全文索引(InnoDB存储引擎)\n",
    "https://www.jianshu.com/p/645402711dac\n",
    "\n",
    "innodb的存储结构\n",
    "https://www.cnblogs.com/janehoo/p/6202240.html\n",
    "\n",
    "深入浅出空间索引：为什么需要空间索引\n",
    "https://www.cnblogs.com/mafeng/p/7909426.html\n",
    "\n",
    "常见的空间索引方法\n",
    "https://blog.csdn.net/Amesteur/article/details/80392679\n",
    "\n",
    "【推荐】pt-online-schema-change解读\n",
    "https://www.cnblogs.com/xiaoyanger/p/6043986.html\n",
    "\n",
    "pt-online-schema-change使用说明、限制与比较\n",
    "https://www.cnblogs.com/erisen/p/5971416.html\n",
    "\n",
    "pt-online-schema-change使用注意要点\n",
    "https://www.jianshu.com/p/84af8b8f040b\n",
    "\n",
    "详细分析MySQL事务日志(redo log和undo log)\n",
    "https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html\n",
    "\n",
    "```\n",
    "\n",
    "---\n",
    "\n",
    "### 1.6.4.MySQL权限相关\n",
    "\n",
    "#### 1.账号权限设置\n",
    "\n",
    "之前在SQL环境篇的时候简单提了一下权限设置（<a href=\"#3.远程访问\">点我回顾</a>），现在再说说常用的权限知识：\n",
    "> <https://www.cnblogs.com/dotnetcrazy/p/9887708.html>\n",
    "\n",
    "##### 1.2.创建账号\n",
    "\n",
    "用户组成格式：`用户名@可访问控制的列表`\n",
    "\n",
    "1. 用户名：一般16字节\n",
    "    - 以`UTF-8`为例：**1英文字符 = 1字节，1中文 = 3字节**\n",
    "2. 可访问控制列表：\n",
    "    - **`%`：所有ip都可访问**（一般都这么干的，数据比较重要的推荐使用第二种）\n",
    "    - **`192.168.1.%`**：`192.168.1`网段的ip都可以访问\n",
    "        - 这个不包含`localhost`（数据库本地服务器不能访问）\n",
    "    - `localhost`：只能通过数据库服务器进行本地访问\n",
    "\n",
    "**1.创建命令：`create user user_name@ip identified by '密码';`**\n",
    "> PS：可以使用`\\h create user`来查看帮助文档\n",
    "\n",
    "![05/05/1.createuser.png](https://img2018.cnblogs.com/blog/1127869/201905/1127869-20190505080238146-567441917.png)\n",
    "\n",
    "```shell\n",
    "mysql> \\h create user;\n",
    "\n",
    "# 新增password_option选项\n",
    "password_option: {\n",
    "    PASSWORD EXPIRE                 #设置密码过期，用户在下次登录时密码设置一个新的密码\n",
    "    PASSWORD EXPIRE DEFAULT         #设置账号使用全局配置的过期策略  default_password_lifetime \n",
    "    PASSWORD EXPIRE NEVER           #密码永不过期\n",
    "    PASSWORD EXPIRE INTERVAL N DAY  #密码在N天后过期\n",
    "    PASSWORD HISTORY DEFAULT        #设置账号使用全局配置的密码历史策略 password_history\n",
    "    PASSWORD HISTORY N              #设置禁止重用最新N次的密码\n",
    "    PASSWORD REUSE INTERVAL DEFAULT #基于时间控制密码是否可以重用，default表示使用全避默认值password-reuse-interval\n",
    "    PASSWORD REUSE INTERVAL N DAY   #基于时间控制密码是否可以重用，禁止重用N天前的密码\n",
    "}\n",
    "```\n",
    "\n",
    "**2.查看当前用户：`select user();`**\n",
    "\n",
    "PS：`MariaDB`**查看当前数据库有哪些用户**：`select user,password,host from mysql.user;`\n",
    "> MySQL：`select user,authentication_string,host from mysql.user;`\n",
    "\n",
    "**3.修改密码：`alter user user() identified by '密码';`**\n",
    "\n",
    "**4.另类思路：我一般都是直接在表中插入数据**（MySQL是`authentication_string`）\n",
    "> eg：`insert into mysql.user(user,host,password) values(\"用户名\",\"%\",password(\"密码\"));`\n",
    "\n",
    "> PS：修改密码：`update mysql.user set `password`=password('新密码') where user='用户名';`\n",
    "\n",
    "知识拓展：<a href=\"https://blog.csdn.net/vurtne_ye/article/details/26514499/\">ERROR 1045 (28000): Access denied for user 'mysql'@'localhost' </a>\n",
    "\n",
    "##### 1.3.常用权限\n",
    "\n",
    "| 权限类别 | 语句           | 说明文字       |\n",
    "| -------- | -------------- | -------------- |\n",
    "| `admin`  | `create user`  | 创建新用户权限 |\n",
    "| -        | `grant option` | 为用户设置权限 |\n",
    "| -        | `super`        | 设置服务器权限 |\n",
    "| `DDL`    | **`create`**   | 创建数据库和表 |\n",
    "| -        | **`alter`**    | 修改表结构权限 |\n",
    "| -        | **`index`**    | 创建和删除索引 |\n",
    "| -        | `drop`         | 删除数据库和表 |\n",
    "| `DML`    | **`select`**   | 查询表数据权限 |\n",
    "| -        | **`insert`**   | 插入表数据权限 |\n",
    "| -        | **`update`**   | 删除表数据权限 |\n",
    "| -        | `execute`      | 可执行存储过程 |\n",
    "| -        | `delete`       | 删除表数据权限 |\n",
    "\n",
    "补充说明：`super`：如设置全局变量等系统语句，一般DBA会有这个权限\n",
    "\n",
    "PS：MariaDB查看数据库支持哪些权限：**`show privileges;`**\n",
    "> <https://mariadb.com/kb/en/library/show-privileges/>\n",
    "\n",
    "##### 1.4.用户授权\n",
    "\n",
    "权限这个东西大家都懂，一般都是最小权限\n",
    "\n",
    "授权命令如下：**`grant 权限列表 on 数据库.表 to 用户名@ip`**\n",
    "> PS：开发的时候可能为了省事这么设置：**`grant all [privileges] on 数据库.* to 用户名@'%';`**\n",
    "\n",
    "正规点一般这么设置：\n",
    "- 线上：`grant select,insert,update on 数据库.* to 用户名@ip`\n",
    "- 开发：`grant select,insert,update,index,alter,create on 数据库.* to 用户名@ip段`\n",
    "\n",
    "**PS：查看当前用户权限：`show grants for 用户名;`，刷新数据库权限：`flush privileges;`**\n",
    "> 以前可以在授权的时候直接创建用户（加一段`identified by '密码'`），新版本好像分开了\n",
    "\n",
    "##### 1.5.权限收回\n",
    "\n",
    "命令如下：**`revoke 权限列表 on 数据库.表 from 用户名@ip`**\n",
    "> eg：`revoke create,alter,delete from django.* from dnt@'%'`（是`from`而不是`on`）\n",
    "\n",
    "#### 2.数据库账号安全\n",
    "\n",
    "这个了解即可，我也是刚从DBA朋友那边了解到的知识（`MySQL8.0`），基本上用不到的，简单罗列下规范：\n",
    "\n",
    "1. 只给最小的权限（线上权限基本上都是给最低的(防黑客)）\n",
    "2. 密码强度限制（MySQL高版本默认有限制，主要针对MariaDB）\n",
    "3. 密码有期限（谨慎使用，不推荐线上用户设置有效期）\n",
    "4. 历史密码不可用（不能重复使用旧密码）\n",
    "    - PS：现在用BAT的产品来修改密码基本上都是不让使用上次的密码\n",
    "\n",
    "**设置前三次使用过的密码不能再使用：`create user@'%'identified by '密码' password history 3;`**\n",
    "\n",
    "PS：**设置用户密码过期：`alter user 用户名@ip password expire;`**\n",
    "\n",
    "#### 3.迁移问题\n",
    "\n",
    "经典问题：**如何从一个实例迁移数据库账号到另一个实例？**\n",
    "- eg：老集群 > 新集群\n",
    "\n",
    "官方文档：<https://www.percona.com/doc/percona-toolkit/LATEST/pt-show-grants.html>\n",
    "\n",
    "##### 3.1.版本相同\n",
    "\n",
    "数据库备份下，然后在新环境中恢复\n",
    "\n",
    "然后**导出用户创建和授权语句**：eg：**`pt-show-grants -u=root,-p=密码,-h=服务器地址 -P=3306`**\n",
    "> 扩展文章：<a href=\"https://www.cnblogs.com/shengdimaya/p/7093030.html\">pt-show-grants的使用</a>（eg：`pt-show-grants --host=192.168.36.123 --port=3306 --user=root --password=密码`）\n",
    "\n",
    "生成的脚本大致是这样的：(**把脚本放新服务器中执行即可**)\n",
    "\n",
    "```sql\n",
    "CREATE USER IF NOT EXISTS 'mysql.sys'@'localhost';\n",
    "ALTER USER 'mysql.sys'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE \n",
    "PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';\n",
    "GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';\n",
    "GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';\n",
    "-- Grants for 'root'@'%'\n",
    "CREATE USER IF NOT EXISTS 'root'@'%';\n",
    "ALTER USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPI\n",
    "RE DEFAULT ACCOUNT UNLOCK;GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;\n",
    "```\n",
    "\n",
    "##### 3.2.版本不同\n",
    "\n",
    "可以使用上面的方法，但是需要使用`mysql_upgrade`升级下系统表（适用：**低版本到高版本**）但是推荐使用**`生成SQL脚本`**\n",
    "> 扩展文章：<a href=\"https://www.cnblogs.com/zengkefu/p/5678054.html\" target=\"_blank\">mysql升级小结和mysql_upgrade的用途</a>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.6.5.MySQL日志相关\n",
    "\n",
    "本文的测试环境：`MySQL5.7.26`、`MariaDB5.5.60`、`MySQL8.0.16`\n",
    "> PS：版本查询`select version();`\n",
    "\n",
    "#### 1.MySQL常用日志\n",
    "\n",
    "服务器层日志（存储引擎层有自己的日志）\n",
    "\n",
    "| 日志类型                       | 描述                                                   |\n",
    "| ------------------------------ | ------------------------------------------------------ |\n",
    "| `error_log`(错误日志)          | 记录MySQL启动、运行或停止时出现的问题                  |\n",
    "| `general_log`(常规日志)        | 记录所有发送给MySQL的请求（耗性能）                    |\n",
    "| **`slow_query_log`**(慢查日志) | 记录符合条件的查询（eg：超过10s、没有使用索引等）      |\n",
    "| **`binary_log`**(二进制日志)   | 记录全部有效的数据修改日志（老版本数据库不会开启）     |\n",
    "| `relay_log`(中继日志)          | 用于主从复制，临时存储主从同步的二进制日志（增量复制） |\n",
    "\n",
    "知识扩展：<https://blog.csdn.net/zhang123456456/article/details/72811875>\n",
    "\n",
    "**实时查看文件：`tail -f /var/log/mysqld.log`**\n",
    "> tail -f 用于监视文件增长（默认是末尾10行）\n",
    "\n",
    "#### 2.error_log（错误日志）\n",
    "\n",
    "一般记录MySQL`运行错误`和和`未授权的访问`\n",
    "> - **老版：`log_error` + `log_warnings`**\n",
    "> - **常用：`log_error` + `log_error_verbosity`**\n",
    "> - 新版：`log_error` + `log_error_verbosity` + `log_error_services`\n",
    "\n",
    "查询MySQL配置：`show variables like '%log_error%';`\n",
    "\n",
    "SQL查询可以这么干：\n",
    "\n",
    "```sql\n",
    "-- Ubuntu下默认是：`/var/log/mysql/error.log`\n",
    "-- CentOS下默认是：`/var/log/mysqld.log` | `/var/log/mariadb/mariadb.log`\n",
    "select @@log_error; -- 尽可能和Data分开存储\n",
    "\n",
    "-- 0：不记录警告信息，1：告警信息写入错误日志，2：各类告警信息都写入（eg:网络故障和重连信息）\n",
    "select @@log_warnings; -- MySQL8中已经移除（MySQL5.7默认是2，MariaDB5.5.60默认是1）\n",
    "\n",
    "-- 错误级别（1：Error，2：Error、Warning，3：Error、Warning、Info\n",
    "select @@log_error_verbosity; -- MySQL8默认是2，MySQL5.7默认是3\n",
    "-- PS：从MySQL 5.7.2开始，首选`log_error_verbosity`系统变量\n",
    "\n",
    "-- 默认是`log_filter_internal; log_sink_internal`\n",
    "select @@log_error_services; -- MySQL8.0中新增\n",
    "```\n",
    "\n",
    "PS：其实MySQL在市面上有很多成熟解决方案（基本上都是基于5.6、5.7的）\n",
    "> 这也是为什么我开篇主推`MySQL5.7`系列和`MariaDB5.5.60`（很多时候不是不用最新DB，而是架构依赖做不到啊）\n",
    "\n",
    "知识拓展：<https://www.cnblogs.com/kerrycode/p/8973285.html>\n",
    "\n",
    "PS：SQLServer的ErrorLog也是差不多的\n",
    "\n",
    "![类比MSSQL的Errorlog.png](https://img2018.cnblogs.com/blog/1127869/201907/1127869-20190709113209497-1463013531.png)\n",
    "\n",
    "##### MySQL8.0新增参数：log_error_services\n",
    "\n",
    "**日志服务组件**：\n",
    "\n",
    "| 日志服务组件名         | 描述                                            |\n",
    "| ---------------------- | ----------------------------------------------- |\n",
    "| `log_sink_internal`    | 默认的日志输出组件（依赖`log_error`）           |\n",
    "| **`log_filter_internal`**  | 默认的日志过滤组件（依赖`log_error_verbosity`） |\n",
    "| **`log_sink_json`**    | 将错误日志输出到`json`文件                      |\n",
    "| `log_sink_syseventlog` | 将错误日志输出到系统日志文件                    |\n",
    "\n",
    "PS：`log_filter_internal`：过滤错误信息（**达不到级别的不记录**）\n",
    "\n",
    "**日记格式一般是这样的**：`UTC时间戳 进程id [日志级别] [错误代码] [由什么产生的日志（Server or Client）] 详细信息`\n",
    "> eg：`2019-05-19T09:54:11.590474Z 8 [Warning] [MY-010055] [Server] IP address '192.168.36.144' could not be resolved: Name or service not known`\n",
    "\n",
    "**一般`log_sink_json`用的比较多**：\n",
    "> 官方文档参考：<https://dev.mysql.com/doc/refman/8.0/en/error-log-json.html>\n",
    "\n",
    "PS：第一次使用需要安装一下json组件：`install component 'file://component_log_sink_json';`\n",
    "> **常用设置：`set persist log_error_services='log_filter_internal;log_sink_json';`**\n",
    "\n",
    "##### 时间戳相关的小知识点\n",
    "\n",
    "上面的时间默认是UTC的时间戳，和我们是有时差的，这个时间戳可以通过设置`log_timestamps`来本地化：\n",
    "\n",
    "```sql\n",
    "-- 查询\n",
    "select @@log_timestamps; -- MySQL5.7新增\n",
    "\n",
    "-- 从8开始,可通过SET PERSIST命令将全局变量的修改持久化到配置文件中\n",
    "set persist log_timestamps='SYSTEM'; -- 需要root权限\n",
    "```\n",
    "\n",
    "**PS：`set persist`生成的配置文件路径在：`/var/lib/mysql/mysqld-auto.cnf`**\n",
    "\n",
    "![autoconfig.png](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190616110826719-129125569.png)\n",
    "\n",
    "#### 3.general_log（常规日志）\n",
    "\n",
    "以前开发调试的时候基本上都是会开启的，上线后关闭（系统V1初期的时候也会开启一段时间）\n",
    "> **现在开发可以使用[go-sniffer](https://www.cnblogs.com/dotnetcrazy/p/10443522.html)来抓包查看客户端执行的SQL**\n",
    "\n",
    "```sql\n",
    "-- 是否打开常规日志（0不打开，1打开）\n",
    "-- 一般不打开（性能）\n",
    "select @@general_log; -- 默认为0\n",
    "\n",
    "-- Ubuntu默认：/var/lib/mysql/ubuntuserver.log\n",
    "-- CentOS默认：/var/lib/mysql/localhost.log\n",
    "select @@general_log_file; -- 常规日志的路径\n",
    "\n",
    "-- 日志的存储方式（FILE | TABLE | NONE）\n",
    "select @@log_output; -- 默认是文件存储\n",
    "```\n",
    "\n",
    "简单看一下常规日志在数据库中的结构：\n",
    "\n",
    "![2.常规日志.png](https://img2018.cnblogs.com/blog/1127869/201905/1127869-20190522182213738-70856259.png)\n",
    "\n",
    "**临时开启参考**：\n",
    "\n",
    "```shell\n",
    "# 开启\n",
    "set global general_log = 1;\n",
    "\n",
    "# set [global | persist] general_log_file = '日志路径';\n",
    "\n",
    "set global log_output = 'TABLE';\n",
    "```\n",
    "\n",
    "#### 4.`slow_query_log`（慢查询日志）\n",
    "\n",
    "这个是`最常用`的，把符合条件的查询语句记录在日志中，**一般都是些需要优化的SQL**\n",
    "> PS：出现性能瓶颈的时候，或者为了优化SQL会开启一段时间（小项目推荐直接开启）\n",
    "\n",
    "先看下默认值：**`show variables like '%slow%';`、`show variables like 'long%';`**\n",
    "\n",
    "![2.慢查询.png](https://img2018.cnblogs.com/blog/1127869/201905/1127869-20190522192146261-1133467961.png)\n",
    "\n",
    "**SQL查询**：\n",
    "\n",
    "```sql\n",
    "-- 是否开启\n",
    "select @@slow_query_log; -- 默认是关闭\n",
    "\n",
    "-- CentOS：/var/lib/mysql/localhost-slow.log\n",
    "-- Ubuntu：/var/lib/mysql/ubuntuserver-slow.log\n",
    "select @@slow_query_log_file;\n",
    "\n",
    "-- 条件：设置超过多少秒为慢查询（一般设置1s）\n",
    "select @@long_query_time; -- 默认是10s（支持小数：0.003)\n",
    "\n",
    "-- PS：设置为0就会记录所有SQL（不推荐这么干）\n",
    "\n",
    "-- 条件：没有使用索引的查询记录到日志中\n",
    "select @@log_queries_not_using_indexes; -- 默认是0（不开启）\n",
    "\n",
    "-- 记录optimize table、analyze table和alter table的管理语句\n",
    "select @@log_slow_admin_statements; -- 默认是0（不开启）\n",
    "\n",
    "-- 记录由Slave所产生的慢查询\n",
    "select @@log_slow_slave_statements;\n",
    "```\n",
    "\n",
    "**常用设置**：\n",
    "> PS：高并发下的互联网项目，对SQL执行时间的容忍度一般都是**低于`300~500ms`**的（`long_query_time=0.05`）\n",
    "\n",
    "```shell\n",
    "# 常用如下：（需要MySQL的root权限）\n",
    "set global slow_query_log = 1; # 开启慢查询日志\n",
    "set global long_query_time = 1; # 记录大于1s的SQL\n",
    "set global log_slow_admin_statements = 1; # 记录管理语句\n",
    "set global log_queries_not_using_indexes = 1; # 记录没有使用索引的SQL\n",
    "# set [global | persist] slow_query_log_file = '路径'; # 设置log路径\n",
    "```\n",
    "\n",
    "**设置`long_query_time`时，需要重新连接才能生效（不需要重启DB）**\n",
    "> PS：当前会话不生效，之后的会话就生效了（不想重连可以再设置下当前会话的`long_query_time`）\n",
    "\n",
    "知识拓展：（`chown mysql:mysql /work/log/xxx.log`）\n",
    "\n",
    "- <https://shihlei.iteye.com/blog/2311752>\n",
    "- <https://www.cnblogs.com/1021lynn/p/5328495.html>\n",
    "\n",
    "#### 扩展：慢查询工具\n",
    "\n",
    "先简单分析下慢查询日志：\n",
    "\n",
    "```shell\n",
    "# Time: 2019-05-22T21:16:28.759491+08:00\n",
    "# User@Host: root[root] @ localhost []  Id:    11\n",
    "# Query_time: 0.000818  Lock_time: 0.000449 Rows_sent: 5  Rows_examined: 5\n",
    "SET timestamp=1558530988;\n",
    "select * from mysql.user order by host; # SQL语句\n",
    "```\n",
    "\n",
    "1. `Time`：查询的**执行时间**（`start_time`）\n",
    "2. `User@Host: root[root] @ localhost []  Id:11`：执行 sql 的**主机信息**\n",
    "3. `Query_time`：SQL**`查询`**所**耗**的**时**间\n",
    "4. `Lock_time`：**锁定时间**\n",
    "5. `Rows_sent`：所**发送的行数**\n",
    "6. `Rows_examined`：**锁扫描的行数**\n",
    "7. `SET timestamp=1558530988;`：SQL**执行时间**\n",
    "\n",
    "现在可以说说工具了，推荐两款：\n",
    "\n",
    "1. 自带的慢日志分析工具：`mysqldumpslow`\n",
    "2. MySQL工具箱（`percona-toolkit`）中的`pt-query-digest`\n",
    "\n",
    "##### mysqldumpslow（精简）\n",
    "\n",
    "**查询最慢的10条SQL：`mysqldumpslow -s t -t 10 /var/lib/mysql/localhost-slow.log`**\n",
    "\n",
    "```shell\n",
    "-s 按照那种方式排序\n",
    "    t: 查询时间\n",
    "    c：访问计数\n",
    "    l：锁定时间\n",
    "    r:返回记录\n",
    "    al：平均锁定时间\n",
    "    ar：平均访问记录数\n",
    "    at：平均查询时间\n",
    "-t 返回多少条数据（可以理解为top n）\n",
    "-g 可以跟上正则匹配模式，大小写不敏感。\n",
    "```\n",
    "\n",
    "PS：使用mysqldumpslow的分析结果不会显示具体完整的sql语句：\n",
    "\n",
    "1. **翻页sql不一样，性能也是不一样的，越往后的页数越容易出现慢查询，而mysqldumpslow把所有翻页sql当成一个sql了**\n",
    "2. eg：`select * from tb_table where uid=20 group by createtime limit 10000, 1000;` ==> `select * from tb_table where uid=N group by createtime limit N, N;`\n",
    "    - 不管你uid和limit怎么变，mysqldumpslow认为是一样的\n",
    "\n",
    "##### pt-query-digest（推荐）\n",
    "\n",
    "官方文档：<https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html>\n",
    "> **分析慢查询日志：`pt-query-digest /var/lib/mysql/localhost-slow.log`**\n",
    "\n",
    "1. 使用tcppdump捕获MySQL协议数据，然后报告最慢的查询：\n",
    "    - `tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt`\n",
    "    - `pt-query-digest --type tcpdump mysql.tcp.txt`\n",
    "2. 查看来自远程进程列表上最慢的查询：\n",
    "    - `pt-query-digest --processlist h=ip`\n",
    "\n",
    "安装可以参考：<https://github.com/lotapp/awesome-tools/blob/master/README.md#4%E8%BF%90%E7%BB%B4>\n",
    "> PS：percona-toolkit的常用工具我也在里面简单说了下，对应文档也贴了\n",
    "\n",
    "##### other\n",
    "\n",
    "PS：还有一款**`mysqlsla`**我没用过，所以贴个参考文章，感兴趣的同志自己研究下\n",
    "> <https://www.cnblogs.com/fengchi/p/6187099.html>\n",
    "\n",
    "知识拓展：<https://www.cnblogs.com/fengchi/p/6187099.html>\n",
    "\n",
    "---\n",
    "\n",
    "#### 5.binary_log（二进制日志）\n",
    "\n",
    "上节主要说了通用日志和慢查日志，今天说下二进制日志：\n",
    "\n",
    "二进制日志算是最常用的了，主要就是**`记录对数据库的修改`**，然后就是**`主从复制`**用的比较多（比如增量备份）\n",
    "> PS：记录了修改操作，那么衍生出的场景就是：`增量备份和恢复`（基于时间点的备份和恢复）\n",
    "\n",
    "PS：MySQL日志主要分为这两类：（互不干扰）\n",
    "\n",
    "1. `服务层`日志（和使用存储引擎无关）\n",
    "    - 通用日志、慢查询日志、二进制日志\n",
    "2. `存储引擎层`日志\n",
    "    - eg：innodb的重做日志(`redo log`)和回滚日志(`undo log`)\n",
    "\n",
    "Q：那什么样的修改会记录下来呢？\n",
    "> A：记录所有对MySQL数据库的修改事件（包括增删改查事件和对表结构修改的事件），而且**只记录已经成功执行的事件**（失败的不会记录）\n",
    "\n",
    "这么说可能有点抽象，熟悉SQLServer的同志看个图就秒懂：\n",
    "\n",
    "![3.二进制日志.png](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615114306346-1188080944.png)\n",
    "\n",
    "##### 5.1.二进制日志格式\n",
    "\n",
    "| 参数        | 说明                                                                        |\n",
    "| ----------- | --------------------------------------------------------------------------- |\n",
    "| `STATEMENT` | 基于段的格式，记录执行数据修改时候所执行的SQL语句                           |\n",
    "| **`ROW`**   | 基于行的格式，记录增删改查操作所修改行的信息（每修改一行就会有一条信息）    |\n",
    "| **`MIXED`** | 基于行和端的混合格式，根据SQL语句由系统决定是基于段还是基于行的日志格式记录 |\n",
    "\n",
    "查看方式：**`show variables like 'binlog_format';`**\n",
    "\n",
    "1. **binlog_format=`statement`**：基于段的记录格式（老版本的默认值）\n",
    "    1. 优点：记录量较小，节约磁盘和网络IO（单条操作Row更节约）\n",
    "    2. 缺点：必须记录上下文信息来保证语句在从服务器上执行结果与主服务器相同\n",
    "        - **但是如果使用了`uuid()`、`user()`等结果非确定的函数，可能会造成MySQL主从不一致**\n",
    "    3. **日志查看**：`mysqlbinlog /var/lib/mysql/binlog.0000xx | more`（不用指定参数）\n",
    "2. **binlog_format=`row`**：基于行的记录格式（5.7以后的默认值）\n",
    "    1. 优点：可以避免MySQL复制中出现的主从不一致的问题（主从更安全）\n",
    "        - PS：没有备份的时候可以通过分析row格式的二进制日志来反向恢复\n",
    "    2. 缺点：记录日志量较大（顺序写入）\n",
    "        - **现在增加了新参数来优化**：`binlog_row_image=[full|minimal|noblob]`\n",
    "    3. **日志查看**：`mysqlbinlog -vv /var/lib/mysql/binlog.0000xx | more`\n",
    "3. **binlog_format=`mixed`**：基于行和端的混合格式（`推荐`）\n",
    "    - PS：数据量大小由所执行的SQL决定（非确定性函数越多，行数越多）\n",
    "\n",
    "PS：**DDL操作（create、drop、alter）的时候都是基于段方式来记录log**\n",
    "> 如果一条一条记录，表有上亿数据，我就修改某列的状态值，那不得疯？\n",
    "\n",
    "**对`binlog_row_image=[FULL|MINIMAL|NOBLOB]`的补充说明**：\n",
    "> PS：**查看方式：`show variables like 'binlog_row_image'`**\n",
    "\n",
    "1. 默认是`full`：完整\n",
    "    - 记录修改行的全部内容\n",
    "2. `noblob`：就是在full记录的基础上对大文本列的优化\n",
    "    - **没有对text或者blob列修改就不记录该列**\n",
    "3. `minimal`：简单记录，**只记录修改的那一列**\n",
    "    - PS：**这个要特别注意一点，虽然容量小了，但是一旦误操作，很难恢复的**（不知道原来内容）\n",
    "\n",
    "##### 推荐使用\n",
    "\n",
    "**一般使用`binlog_format=mixed`混合格式** or **`binlog_format=row`** + **`binlog_row_image=minimal`**\n",
    "> PS：如果对安全性要求特别高，推荐使用`binlog_format=row` + `binlog_row_image=full`（不怕误操作）\n",
    "\n",
    "这个和SQLServer的日志恢复模式有点类似，我贴下图你们可以对比参考：\n",
    "\n",
    "![3.容量.png](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615114311864-1704843102.png)\n",
    "\n",
    "##### 5.2.二进制日志配置\n",
    "\n",
    "上面虽然说完了二进制日志的常用3种格式，但老版本默认都是不启用二进制日志的，咋办？\n",
    "> PS：如果是MariaDB可以去示例配置中查看：`ls /usr/share/mysql/ |grep .cnf`（CentOS）\n",
    "\n",
    "验证下：\n",
    "\n",
    "MySQL8之前：`cat /etc/mysql/mysql.conf.d/mysqld.cnf`（UbuntuServer）\n",
    "\n",
    "![3.binlog.png](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615105438869-1854139695.png)\n",
    "\n",
    "MySQL8：`cat /etc/my.cnf |grep log`（CentOS）\n",
    "![3.binlog2.png](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615105953261-70366363.png)\n",
    "\n",
    "---\n",
    "\n",
    "Q：有些人可能疑惑了，为什么用**`show variables like 'log_bin';`**查询出来的结果和配置文件中不大一样啊？\n",
    "> PS：一般配置项中的参数都可以使用`show variables like 'xx'`来查询对应的值\n",
    "\n",
    "![3.log_bin.png](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615112651069-338309897.png)\n",
    "\n",
    "A：那是因为5.7之后版本分成了两个参数：**`log_bin`和`log_bin_basename`**：\n",
    "> PS：**配置文件的`log_bin=xxx`相当于命令中的`log_bin`和`log_bin_basename`**\n",
    "\n",
    "```\n",
    "mysql> show variables like 'log_bin%';\n",
    "+---------------------------------+-----------------------------+\n",
    "| Variable_name                   | Value                       |\n",
    "+---------------------------------+-----------------------------+\n",
    "| log_bin                         | ON                          |\n",
    "| log_bin_basename                | /var/lib/mysql/binlog       |\n",
    "| log_bin_index                   | /var/lib/mysql/binlog.index |\n",
    "| log_bin_trust_function_creators | OFF                         |\n",
    "| log_bin_use_v1_row_events       | OFF                         |\n",
    "+---------------------------------+-----------------------------+\n",
    "5 rows in set (0.00 sec)\n",
    "```\n",
    "\n",
    "##### 开启演示\n",
    "\n",
    "**MariaDB开启binlog图示**：（CentOS）\n",
    "\n",
    "![4.MariaDB开启binlog.jpg](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615215014750-902512611.jpg)\n",
    "\n",
    "**MySQL5.7演示**：（UbuntuServer）\n",
    "\n",
    "![4.UbuntuServer下MySQL5.7演示.jpg](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615215118756-1227225512.jpg)\n",
    "\n",
    "配置文件中修改：（**`show variables like 'binlog_format';`：查看当前binlog基于什么格式**）\n",
    "\n",
    "```shell\n",
    "# 服务器标识\n",
    "server-id=1 # 单机MariaDB可不开启\n",
    "\n",
    "# 开启binlog并设置路径\n",
    "# 不指定路径则默认在数据目录下\n",
    "log_bin=binlog # 这个代表以binlog开头的文件\n",
    "\n",
    "# binlog采用ROW|MIXED格式\n",
    "# binlog_format=MIXED # 5.7默认是ROW\n",
    "```\n",
    "\n",
    "先看下文件前缀（`log_bin=binlog`）的概念，一张图就懂：\n",
    "\n",
    "![4.文件前缀.png](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615223600204-780006208.png)\n",
    "\n",
    "PS：**如果log_bin只是指定一个名字，那么默认路径一般都是在数据文件的文件夹中**\n",
    "> 配置文件一般都会写，eg:`datadir=/var/lib/mysql`，或者通过**`show variables like 'datadir';`**也可以查询到\n",
    "\n",
    "虽然和SQLServer文件组不是一个概念，但有些相似 ==> `log可以多个也可以动态调整`\n",
    "![3.多日志文件.png](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615114910706-1077844738.png)\n",
    "\n",
    "##### 5.3.ROW模式下记录SQL\n",
    "\n",
    "Q：虽然ROW记录能保证主从数据安全，但我们排查问题的时候往往需要知道SQL，而用段的记录方式又不合适，咋办？\n",
    "\n",
    "A：有个新参数可以解决：**`binlog_rows_query_log_events`**，开启后就可以记录sql了\n",
    "\n",
    "查看方式：**`show variables like 'binlog_row%';`**\n",
    "\n",
    "```shell\n",
    "mysql> show variables like 'binlog_row%';\n",
    "+------------------------------+-------+\n",
    "| Variable_name                | Value |\n",
    "+------------------------------+-------+\n",
    "| binlog_row_image             | FULL  |\n",
    "| binlog_rows_query_log_events | OFF   |\n",
    "+------------------------------+-------+\n",
    "2 rows in set (0.01 sec)\n",
    "```\n",
    "\n",
    "##### binlog演示\n",
    "\n",
    "**显示binlog列表：`show binary logs;`**\n",
    "\n",
    "**刷新一份新的binlog：`flush logs;`**（现在开始的二进制日志就记录在这个新文件中）\n",
    "\n",
    "![5.新建binlog.jpg](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615230123267-704005638.jpg)\n",
    "\n",
    "binlog现在是空的：(`-vv`：把二进制格式的日志显示为能读懂的字符串)\n",
    "> **`mysqlbinlog --no-defaults -vv --base64-output=DECODE-ROWS /var/lib/mysql/binlog.000006`**\n",
    "\n",
    "![5.新建binlog2.png](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615230220876-63662242.png)\n",
    "\n",
    "现在简单摸拟几个SQL操作，然后看看binlog：\n",
    "\n",
    "![6.sql.png](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615230537927-1686327416.png)\n",
    "\n",
    "查看下binlog日志：(线上开发一般都是FULL模式，主要是防止程序员修改SQL的时候不加条件等误操作)\n",
    "> FULL模式就是这样，该行数据全部记录（修改部分其实就绿色框的地方）\n",
    "\n",
    "![6.binlog.jpg](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615231013171-515634847.jpg)\n",
    "\n",
    "想要binlog中记录SQL就开启`binlog_rows_query_log_events`：\n",
    "> PS：像这种操作，如果模式选混合模式，binlog中会记录SQL的\n",
    "\n",
    "临时开启下`binlog_rows_query_log_events`（如果你有需要可以配置文件设置一下）\n",
    "> PS：MySQL8可通过`set persist`命令将全局变量的修改持久化到配置文件中\n",
    "\n",
    "![6.sql2.png](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615231626834-759896379.png)\n",
    "\n",
    "效果如下：\n",
    "\n",
    "![6.binlog2.png](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615231542008-1771345221.png)\n",
    "\n",
    "##### 5.4.二进制日志的清除\n",
    "\n",
    "1. 自动清除\n",
    "    - 配置文件中设置时间：`expire_logs_days = 30`\n",
    "2. 手动清除\n",
    "    - 删除指定编号之前的日志：`purge binary logs to 'binlog.000006';`\n",
    "    - **删除指定时间之前的日志：`purge binary logs before '2019-06-15 14:14:00';`**\n",
    "\n",
    "已经23:23了，我们快速演示下：\n",
    "\n",
    "MySQL命令行中执行命令：\n",
    "\n",
    "![7.删除.png](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615232721720-252354903.png)\n",
    "\n",
    "文件列表：\n",
    "\n",
    "![7.文件.png](https://img2018.cnblogs.com/blog/1127869/201906/1127869-20190615232749442-1177374128.png)\n",
    "\n",
    "##### 5.5.二进制日志与主从\n",
    "\n",
    "这个把运维篇讲完会继续说，运维篇结束后会有个高级篇（架构），这边就简单提下`二进制格式对主从复制的影响`：\n",
    "\n",
    "1. 基于SQL语句的复制（SBR）\n",
    "    - 二进制日志格式使用的是`statement`格式（5.7前的默认）\n",
    "2. 基于行的复制（RBR）\n",
    "    - 二进制日志格式使用的是基于行的日志格式\n",
    "3. 混合模式\n",
    "    - 根据实际在上面两者中切换\n",
    "\n",
    "贴个课后拓展文章：<https://www.cnblogs.com/gujianzhe/p/9371682.html>\n",
    "\n",
    "---\n",
    "\n",
    "#### 6.relay_log（中继日志）\n",
    "\n",
    "临时记录从主服务器中增量复制到从服务器中的binlog\n",
    "\n",
    "```shell\n",
    "relay_log = 文件前缀 # 默认是主机名，换主机容易变，一般自己设置一个\n",
    "\n",
    "relay_log_purge = 1 # 默认是on（1）自动清理\n",
    "```\n",
    "\n",
    "**下级预估：备份与恢复、监控**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### 4.6.6.SQLServer监控\n",
    "\n",
    "脚本示意：<https://github.com/lotapp/BaseCode/tree/master/database/SQL/SQLServer>\n",
    "> PS：这些脚本都是我以前用SQLServer手写的，参考即可（现在用MySQL，下次也整理一下）\n",
    "\n",
    "之前写SQLServer监控系列文章因为换环境断篇了，只是简单演示了下基础功能，现在准备写`MySQL`监控相关内容了，于是补了下：\n",
    "> **[SQLServer性能优化之---数据库级日记监控](https://www.cnblogs.com/dunitian/p/6022967.html)**：<https://www.cnblogs.com/dunitian/p/6022967.html>\n",
    "\n",
    "在说监控前你可以先看下[数据库发邮件](https://mp.weixin.qq.com/s/WWdDSNj_19RVCZJMGxhMWA)：<https://www.cnblogs.com/dunitian/p/6022826.html>\n",
    "> 应用：**一般就是设置个定时任务，把耗时SQL信息或者错误信息通过邮件的方式及时预警**\n",
    "\n",
    "好处就太多了，eg：客户出错如果是数据库层面，那瞬间就可以场景重放（PS：等客户找会降低业绩）\n",
    "\n",
    "以往都是程序的`try`+`catch`来捕获错误，但数据库定时任务之类的出错程序是捕获不到的，所以就需要数据库层面的监控了\n",
    "> PS：开发的时候通过`SQLServer Profiler`来监控\n",
    "\n",
    "先说说本质吧：SQLServer2012的XEVENT机制已经完善，eg：常用的扩展事件**`error_reported`就可以在错误的时候通过邮件来通知管理员了**\n",
    "> PS：扩展事件性能较高，而且比较轻量级\n",
    "\n",
    "PS：**SQLServer的监控大体思路三步走：`发邮件`，`事件监控`，`定时执行`**\n",
    "\n",
    "#### 4.6.6.1 发送邮件\n",
    "\n",
    "这个之前讲过，这边就再说下SQL的方式：\n",
    "\n",
    "##### 1.配置发件人邮箱\n",
    "\n",
    "这个配置一次即可，**以后使用就可以直接通过配置名发邮件**：\n",
    "\n",
    "```sql\n",
    "--开启发邮件功能\n",
    "exec sp_configure 'show advanced options',1\n",
    "reconfigure with override\n",
    "go\n",
    "exec sp_configure 'database mail xps',1\n",
    "reconfigure with override\n",
    "go\n",
    "\n",
    "--创建邮件帐户信息\n",
    "exec msdb.dbo.sysmail_add_account_sp\n",
    "  @account_name ='dunitian',                     -- 邮件帐户名称\n",
    "  @email_address ='xxx@163.com',                 -- 发件人邮件地址\n",
    "  @display_name ='SQLServer2014_192.168.36.250', -- 发件人姓名\n",
    "  @MAILSERVER_NAME = 'smtp.163.com',             -- 邮件服务器地址\n",
    "  @PORT =25,                                     -- 邮件服务器端口\n",
    "  @USERNAME = 'xxx@163.com',                     -- 用户名\n",
    "  @PASSWORD = '邮件密码或授权码'                 -- 密码（授权码）\n",
    "GO\n",
    "\n",
    "--数据库配置文件\n",
    "exec msdb.dbo.sysmail_add_profile_sp\n",
    "  @profile_name = 'SQLServer_DotNetCrazy',       -- 配置名称\n",
    "  @description = '数据库邮件配置文件'            -- 配置描述\n",
    "go\n",
    "\n",
    "--用户和邮件配置文件相关联\n",
    "exec msdb.dbo.sysmail_add_profileaccount_sp\n",
    "  @profile_name = 'SQLServer_DotNetCrazy',     -- 配置名称\n",
    "  @account_name = 'dunitian',                  -- 邮件帐户名称\n",
    "  @sequence_number = 1                         -- account 在 profile 中顺序（默认是1）\n",
    "go\n",
    "\n",
    "```\n",
    "\n",
    "##### 2.发生预警邮箱\n",
    "\n",
    "同样我只演示SQL的方式，图形化的方式可以看我以前写的文章：\n",
    "\n",
    "```sql\n",
    "-- 发邮件测试\n",
    "exec msdb.dbo.sp_send_dbmail\n",
    "@profile_name = 'SQLServer_DotNetCrazy',\t     --配置名称\n",
    "@recipients = 'xxx@qq.com',\t\t\t          --收件邮箱\n",
    "@body_format = 'HTML',\t\t\t\t\t\t   --内容格式\n",
    "@subject = '文章标题',\t\t\t\t\t\t   --文章标题\n",
    "@body = '邮件内容<br/><h2>This is Test</h2>...' --邮件内容\n",
    "```\n",
    "\n",
    "效果：\n",
    "\n",
    "![06-10/1.mail.png](https://img2018.cnblogs.com/blog/1127869/201907/1127869-20190710092137832-648681692.png)\n",
    "\n",
    "##### 3.邮件查询相关\n",
    "\n",
    "主要用途其实就是出错排查：\n",
    "\n",
    "```sql\n",
    "-- 查询相关\n",
    "select * from msdb.dbo.sysmail_allitems     --查看所有邮件消息\n",
    "select * from msdb.dbo.sysmail_mailitems    --查看邮件消息（更多列）\n",
    "\n",
    "select * from msdb.dbo.sysmail_sentitems    --查看已发送的消息\n",
    "select * from msdb.dbo.sysmail_faileditems  --失败状态的消息\n",
    "select * from msdb.dbo.sysmail_unsentitems  --看未发送的消息\n",
    "\n",
    "select * from msdb.dbo.sysmail_event_log    --查看记录日记\n",
    "```\n",
    "\n",
    "---\n",
    "\n",
    "#### 4.6.6.2.监控实现\n",
    "\n",
    "会了邮件的发送，那下面就是监控了\n",
    "\n",
    "##### 1.图形化演示\n",
    "\n",
    "**不推荐使用图形化的方式，但可以来理解扩展事件的监控**\n",
    "\n",
    "1.新建一个会话向导（熟悉后可以直接新建会话）\n",
    "\n",
    "![1.新建会话向导.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190709113824963-184950117.png)\n",
    "\n",
    "![1.新建会话向导2.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190709113839093-2043533499.png)\n",
    "\n",
    "2.设置需要捕获的扩展事件\n",
    "\n",
    "![2.设置需要捕获的扩展事件.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190709113847118-1849537509.png)\n",
    "\n",
    "3.这边捕获的全局字段和左边SQL是一样的（截图全太麻烦了，所以偷个懒，后面会说怎么生成左边的核心SQL）\n",
    "\n",
    "![3.捕获的全局字段.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190710202014593-1923871035.png)\n",
    "\n",
    "4.自己根据服务器性能设置一个合理的值（IO、内存、CPU）\n",
    "\n",
    "![4.设置.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190709114107562-436697123.png)\n",
    "\n",
    "5.生成核心SQL（我们图形化的目的就是生成核心SQL，后面可以根据这个SQL自己扩展）\n",
    "\n",
    "![5.生成核心SQL.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190709114159568-1118574816.png)\n",
    "\n",
    "6.核心代码如下\n",
    "\n",
    "![6.核心代码.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190709114233197-2051660041.png)\n",
    "\n",
    "7.启动会话后一个简单的扩展事件监控就有了\n",
    "\n",
    "![7.启动会话.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190709114247514-105984255.png)\n",
    "\n",
    "8.SQLServer提供了查看方式\n",
    "\n",
    "![8.查看.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190709114556607-442089908.png)\n",
    "\n",
    "9.日志可以自己查下`xxx\\Microsoft SQL Server\\MSSQL12.MSSQLSERVER\\MSSQL\\Log`\n",
    "\n",
    "![9.日志.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190709114642900-1128124868.png)\n",
    "\n",
    "\n",
    "---\n",
    "\n",
    "##### 2.SQL的方式\n",
    "\n",
    "上面只是过家家，主要目的就是让大家知道核心SQL是怎么来的，凭什么这么写\n",
    "\n",
    "下面就来个制定化监控：\n",
    "\n",
    "先截图演示下各个核心点，然后贴一个我封装的存储过程附件\n",
    "\n",
    "1.扩展事件相关的核心代码\n",
    "\n",
    "![1.扩展事件相关的核心代码.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190710202909085-194543718.png)\n",
    "\n",
    "2.内存中数据存储到临时表\n",
    "\n",
    "![2.内存中数据存储到临时表.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190710203015566-1779795363.png)\n",
    "\n",
    "3.临时表中的数据存储到自己建立的表中\n",
    "> 我抛一个课后小问给大家：为什么先存储在临时表中？（提示：效率）\n",
    "\n",
    "![3.临时表中的数据存储到自己建立的表中.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190710203218702-372682658.png)\n",
    "\n",
    "4.发送监控提醒的邮件\n",
    "\n",
    "![4.发送监控提醒的邮件.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190710203500003-1349896179.png)\n",
    "\n",
    "5.看看数据库层面多了什么：\n",
    "\n",
    "![5.看看数据库层面.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190710204033110-148118196.png)\n",
    "\n",
    "6.来个测试\n",
    "\n",
    "![6.测试.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190710204128374-1279485990.png)\n",
    "\n",
    "7.效果（可以自己美化）\n",
    "\n",
    "![7.效果.png](https://img2018.cnblogs.com/blog/658978/201907/658978-20190710204254347-869855125.png)\n",
    "\n",
    "##### SQL附录\n",
    "\n",
    "```sql\n",
    "-- 切换到需要监控的数据库\n",
    "USE [dotnetcrazy]\n",
    "GO\n",
    "\n",
    "--收集服务器上逻辑错误的信息\n",
    "SET QUOTED_IDENTIFIER ON\n",
    "SET ANSI_NULLS ON\n",
    "GO\n",
    "\n",
    "-- 自定义的错误信息表\n",
    "IF OBJECT_ID('log_error_message') IS NULL\n",
    "BEGIN\n",
    "\tCREATE TABLE [dbo].[log_error_message]\n",
    "\t(\n",
    "\t[login_message_id] [uniqueidentifier] NULL CONSTRAINT [DF__PerfLogic__Login__7ACA4E21] DEFAULT (newid()),\n",
    "\t[start_time] [datetime] NULL,\n",
    "\t[database_name] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NULL,\n",
    "\t[message] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL,\n",
    "\t[sql_text] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL,\n",
    "\t[alltext] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL,\n",
    "\t-- [worker_address] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL,\n",
    "\t[username] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL,\n",
    "\t[client_hostname] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL,\n",
    "\t[client_app_name] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL\n",
    "\t) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]\n",
    "END\n",
    "GO\n",
    "\n",
    "-- 创建存储过程\n",
    "CREATE PROCEDURE [dbo].[event_error_monitor]\n",
    "AS\n",
    "    IF NOT EXISTS( SELECT 1 FROM sys.dm_xe_sessions dxs(NOLOCK) WHERE name = 'event_error_monitor') -- 不存在就创建EVENT\n",
    "        -- 创建扩展事件，并把数据放入内存中\n",
    "        BEGIN\n",
    "            CREATE EVENT session event_error_monitor on server\n",
    "            ADD EVENT sqlserver.error_reported -- error_reported扩展事件\n",
    "            (\n",
    "            ACTION -- 返回结果\n",
    "            (\n",
    "            sqlserver.session_id, -- 会话id\n",
    "            sqlserver.plan_handle, -- 计划句柄，可用于检索图形计划\n",
    "            sqlserver.tsql_stack, -- T-SQ堆栈信息\n",
    "            package0.callstack, -- 当前调用堆栈\n",
    "            sqlserver.sql_text, -- 遇到错误的SQL查询\n",
    "            sqlserver.username, -- 用户名\n",
    "            sqlserver.client_app_name, -- 客户端应用程序名称\n",
    "            sqlserver.client_hostname, -- 客户端主机名\n",
    "            -- sqlos.worker_address, -- 当前任务执行时间\n",
    "            sqlserver.database_name -- 当前数据库名称\n",
    "            )\n",
    "            WHERE severity >= 11 AND Severity <=16 -- 指定用户级错误\n",
    "            )\n",
    "            ADD TARGET package0.ring_buffer -- 临时放入内存中\n",
    "            WITH (max_dispatch_latency=1seconds)\n",
    "\n",
    "            -- 启动监控事件\n",
    "            ALTER EVENT SESSION event_error_monitor on server state = START\n",
    "        END\n",
    "    ELSE\n",
    "        -- 存储过程已经存在就把数据插入表中\n",
    "        BEGIN\n",
    "            -- 将内存中已经收集到的错误信息转存到临时表中（方便处理）\n",
    "            SELECT\n",
    "                DATEADD(hh,\n",
    "                        DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),\n",
    "                        n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],\n",
    "                n.value('(event/action[@name=\"database_name\"]/value)[1]', 'nvarchar(128)') AS [database_name],\n",
    "                n.value('(event/action[@name=\"sql_text\"]/value)[1]', 'nvarchar(max)') AS [sql_text],\n",
    "                n.value('(event/data[@name=\"message\"]/value)[1]', 'nvarchar(max)') AS [message],\n",
    "                n.value('(event/action[@name=\"username\"]/value)[1]', 'nvarchar(max)') AS [username],\n",
    "                n.value('(event/action[@name=\"client_hostname\"]/value)[1]', 'nvarchar(max)') AS [client_hostname],\n",
    "                n.value('(event/action[@name=\"client_app_name\"]/value)[1]', 'nvarchar(max)') AS [client_app_name],\n",
    "                n.value('(event/action[@name=\"tsql_stack\"]/value/frames/frame/@handle)[1]', 'varchar(max)') AS [tsql_stack],\n",
    "                n.value('(event/action[@name=\"tsql_stack\"]/value/frames/frame/@offsetStart)[1]', 'int') AS [statement_start_offset],\n",
    "                n.value('(event/action[@name=\"tsql_stack\"]/value/frames/frame/@offsetEnd)[1]', 'int') AS [statement_end_offset]\n",
    "            into #error_monitor -- 临时表\n",
    "            FROM\n",
    "            (    SELECT td.query('.') as n\n",
    "                FROM\n",
    "                (\n",
    "                    SELECT CAST(target_data AS XML) as target_data\n",
    "                    FROM sys.dm_xe_sessions AS s\n",
    "                    JOIN sys.dm_xe_session_targets AS t\n",
    "                        ON t.event_session_address = s.address\n",
    "                    WHERE s.name = 'event_error_monitor'\n",
    "                    --AND t.target_name = 'ring_buffer'\n",
    "                ) AS sub\n",
    "                CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(td)\n",
    "            ) as TAB\n",
    "\n",
    "            -- 把数据存储到自己新建的表中（有SQL语句的直接插入到表中）\n",
    "            INSERT INTO log_error_message(start_time,database_name,message,sql_text,alltext,username,client_hostname,client_app_name)\n",
    "            SELECT TIMESTAMP,database_name,[message],sql_text,'',username,client_hostname,client_app_name\n",
    "            FROM #error_monitor as a\n",
    "            WHERE a.sql_text != '' --AND client_app_name !='Microsoft SQL Server Management Studio - 查询'\n",
    "            AND a.MESSAGE NOT LIKE '找不到会话句柄%' AND a.MESSAGE NOT LIKE '%SqlQueryNotification%' --排除server broker\n",
    "            AND a.MESSAGE NOT LIKE '远程服务已删除%'\n",
    "\n",
    "            -- 插入应用执行信息（没有SQL的语句通过句柄查询下SQL）\n",
    "            INSERT INTO log_error_message(start_time,database_name,message,sql_text,alltext,username,client_hostname,client_app_name)\n",
    "            SELECT TIMESTAMP,database_name,[message],\n",
    "            SUBSTRING(qt.text,a.statement_start_offset/2+1,\n",
    "                        (case when a.statement_end_offset = -1\n",
    "                        then DATALENGTH(qt.text)\n",
    "                        else a.statement_end_offset end -a.statement_start_offset)/2 + 1) sql_text,qt.text alltext,\n",
    "            username,client_hostname,client_app_name\n",
    "            FROM #error_monitor as a\n",
    "            CROSS APPLY sys.dm_exec_sql_text(CONVERT(VARBINARY(max),a.tsql_stack,1)) qt -- 通过句柄查询具体的SQL语句\n",
    "            WHERE a.sql_text IS NULL AND tsql_stack != '' --AND client_app_name = '.Net SqlClient Data Provider'\n",
    "\n",
    "            DROP TABLE #error_monitor -- 删除临时表\n",
    "\n",
    "            --重启清空\n",
    "            ALTER EVENT SESSION event_error_monitor ON SERVER STATE = STOP\n",
    "            ALTER EVENT SESSION event_error_monitor on server state = START\n",
    "        END\n",
    "\n",
    "    -- 美化版预警邮箱\n",
    "    DECLARE @body_html VARCHAR(max)\n",
    "    set @body_html = '<table style=\"width:100%\" cellspacing=\"0\"><tr><td colspan=\"6\" align=\"center\" style=\"font-weight:bold;color:red\">数据库错误监控</td></tr>'\n",
    "    set @body_html = @body_html + '<tr style=\"text-align: left;\"><th>运行时间</th><th>数据库</th><th>发生错误的SQL语句</th><th>消息</th><th>用户名</th><th>应用</th><th>应用程序名</th></tr>'\n",
    "    -- 格式处理（没内容就空格填充）\n",
    "    select @body_html = @body_html + '<tr><td>'\n",
    "        + case (isnull(start_time, '')) when '' then '&nbsp;' else convert(varchar(20), start_time, 120) end + '</td><td>'\n",
    "        + case (isnull(database_name, '')) when '' then '&nbsp;' else database_name end + '</td><td>'\n",
    "        + case (isnull(sql_text, '')) when '' then '&nbsp;' else sql_text end + '</td><td>'\n",
    "        + case (isnull(message, '')) when '' then '&nbsp;' else message end + '</td><td>'\n",
    "        + case (isnull(username, '')) when '' then '&nbsp;' else username end + '</td><td>'\n",
    "        + case (isnull(client_hostname, '')) when '' then '&nbsp;' else client_hostname end + '</td><td>'\n",
    "        + case (isnull(client_app_name, '')) when '' then '&nbsp;' else client_app_name end + '</td></tr>'\n",
    "    from (\n",
    "             select start_time, database_name,sql_text, message, username, client_hostname, client_app_name\n",
    "             from [dbo].[log_error_message]\n",
    "             where start_time >= dateadd(hh,-2,getdate()) -- 当前时间 - 定时任务的时间间隔（2h）\n",
    "               and client_app_name != 'Microsoft SQL Server Management Studio - 查询' -- and client_hostname in('')\n",
    "         ) as temp_message\n",
    "    set @body_html= @body_html+'</table>'\n",
    "\n",
    "    -- 发送警告邮件\n",
    "    exec msdb.dbo.sp_send_dbmail\n",
    "    @profile_name = 'SQLServer_DotNetCrazy',         --配置名称\n",
    "    @recipients = 'xxxxx@qq.com',                  --收件邮箱\n",
    "    @body_format = 'HTML',                           --内容格式\n",
    "    @subject = '数据库监控通知',                       --文章标题\n",
    "    @body = @body_html --邮件内容\n",
    "go\n",
    "\n",
    "```\n",
    "\n",
    "下节预估：定时任务、完整版监控\n",
    "> PS：估计先得更八字的文章（拖太久）然后更完SQLServer更MySQL，等MySQL监控更完会说下备份与恢复，接着我们开架构篇（MyCat系列先不讲放在Redis和爬虫系列的后面）\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "#### 4.6.6.1 定时任务\n",
    "\n",
    "一般定时任务都是程序层面来控制，这次简单说下DB层面的定时任务：\n",
    "\n",
    "```sql\n",
    "-- 定时任务只要执行下存储过程就可以实现数据库监控了\n",
    "select 1/0; -- eg：摸拟一个错误\n",
    "exec event_error_monitor;\n",
    "```\n",
    "\n",
    "---\n",
    "\n",
    "#### 其他监控思路\n",
    "\n",
    "SQLServer是支持编写插件的，你可以和定时任务结合起来，**通过插件发送给程序，通过ES之类的收集慢查询SQL信息或者错误信息**等，反正思路是无限的\n",
    "> PS：你搜“在SQL Server中调用.NET程序集”就一大堆资料了（使用`xp_cmdshell`直接执行程序也是一种思路）\n",
    "\n",
    "贴一个参考文章：<https://www.cnblogs.com/knowledgesea/p/4617877.html>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### 4.6.6.MySQL监控指标\n",
    "\n",
    "这边我们说说`MySQL`的监控：MySQL社区版不提供监控工具，MariaDB有`Monyog`（收费）\n",
    "> 系统规模大了基本上都是使用企业级监控，小了都是写个脚本定期查看下各项监控指标\n",
    "\n",
    "公司以前基本上都是使用**`Zabbix`**和**`Nagios`**\n",
    "> PS：现在中小企更欢喜小米的**`Open-Falcon`**（潜力股）\n",
    "\n",
    "软件使用这块后面有机会再聊，我们今天主要说说MySQL中那些需要知道的监控**指标**\n",
    "> PS：**软件也是根据这些指标来监控的**，你完全可以根据这些自己写个轻量级监控程序\n",
    "\n",
    "数据库监控基本上都是这几方面来进行的：\n",
    "\n",
    "1. DB可用性监控\n",
    "    - 进程存在，能对外服务（能执行SQL）\n",
    "2. DB性能的监控\n",
    "    - QPS、TPS、并发线程数（小于数据库连接数）、缓存命中率\n",
    "3. DB异常的监控\n",
    "    - innodb阻塞和死锁、慢查询\n",
    "4. 主从相关监控\n",
    "    - 链路状态、主从延迟、数据一致性（定期检查）\n",
    "5. 服务器的监控\n",
    "    - CPU、内存、swap分区（内存不够的时候它来顶包）、网络IO、磁盘空间（数据目录和日志目录）\n",
    "\n",
    "#### 4.6.6.1.DB可用性监控\n",
    "\n",
    "这个其实只要判断DB能对外服务就说明DB是可用的（三步走，1可连接，2可读写，3连接数）\n",
    "\n",
    "##### 1.是否可连接\n",
    "\n",
    "查看数据库是否可以连接自带的工具就可以实现了：（也可以使用`telnet ip port`来测试）\n",
    "> 在另一台服务器上运行：`mysqladmin -u用户名 -p -h服务器ip地址 ping`（显示`mysqld is alive`）\n",
    "\n",
    "![0.ping.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190825213009630-1497109802.png)\n",
    "\n",
    "![0.telnet.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190825213331591-2136474978.png)\n",
    "\n",
    "如果Win没安装可以安装一下：\n",
    "\n",
    "![0.win.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190825213512395-1360645751.png)\n",
    "\n",
    "**其实最好就是通过程序看看能不能connect数据库，能就没问题，不能就over了**（你通过IDE也一样，程序的优点在于可自动化）\n",
    "> PS：可以**周期性**的通过程序建立连接，然后执行一个简单查询`select @@version;`，能得到返回结果就行了\n",
    "\n",
    "##### 2.是否可读写\n",
    "\n",
    "一般看能否读写都是简单的查询或者写入（太复杂的容易耗费服务器资源）\n",
    "\n",
    "1. 检查是否可读：执行一个简单的查询即可\n",
    "    - eg:`select @@version;`\n",
    "2. 检查是否可写：创建一个监控表，对表中数据进行update\n",
    "    - eg：更新最近监控时间\n",
    "\n",
    "扩：如果启用了主从服务，那么需要额外检查下`read_only`的配置：\n",
    "> PS：**检查**主从服务器的**主数据库`read_only`是否为off**（从库一般都是设置只读，如果切换了数据库每改过来就不能写了）\n",
    "\n",
    "##### 3.监控连接数\n",
    "\n",
    "主要是两种情况：\n",
    "\n",
    "1. 当前连接数**一直逼近最大连接数**：配置优化 or 提升硬件\n",
    "2. 当前连接数**短时间爆增**：发警告，防止CC之类的安全攻击\n",
    "    - eg：**`当前连接数 / 最大连接数 > 80%`** 预警一下，DBA可以查看是因为啥\n",
    "\n",
    "**最大连接数**：`show variables like 'max_connections';`\n",
    "\n",
    "**当前连接数**：`show global status like 'Threads_connected';`\n",
    "\n",
    "![0.conn_count.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190825185907118-1805383369.png)\n",
    "\n",
    "---\n",
    "\n",
    "#### 4.6.6.2.DB性能的监控（重要）\n",
    "\n",
    "**记录**性能监控过程中**采集到DB的status信息**（以后分析趋势会用到）\n",
    "\n",
    "##### 1.TPS and QPS\n",
    "\n",
    "**`QPS`**（Queries Per Second）:**每秒钟处理的请求数**（一般都是查询，但DML、DDL也包括）\n",
    "> PS：贴一下专业的计算公式：`QPS = (Queries2 - Queries1) / 时间间隔`\n",
    "\n",
    "**`TPS`**（Transactions Per Second）:**每秒钟处理事务的数量**（insert、update、delete）\n",
    "> PS：贴一下专业的计算公式：**`TPS = (TC2 - TC1) / 时间间隔`**（**`TC = Com_insert + Com_delete + Com_update`**）\n",
    "\n",
    "PS：**你可以理解为`TPS`是`QPS`的子集**，我画个草图表示一下:\n",
    "\n",
    "![0.qps_and_tps.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190825184316684-632297659.png)\n",
    "\n",
    "##### 2.计算\n",
    "\n",
    "大体思路：**通过前后两次采集系统状态变量，然后套公式计算**\n",
    "> PS：QPS一般是测一下查频繁程度，TPS是测一下写的频繁程度\n",
    "\n",
    "**`QPS = (Queries2 - Queries1) / 时间间隔`**\n",
    "> PS：Queries = **`show global status like 'Queries';`**\n",
    "\n",
    "![1.q1.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190825182959473-1482902882.png)\n",
    "\n",
    "查询命令：**`show global status where variable_name in ('Queries','uptime');`**\n",
    "\n",
    "![2.qps.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190825183556397-775205966.png)\n",
    "\n",
    "PS：其实`Queries`是MySQL为我们提供的简便方法，本质还是使用了计数器的求和`Sum(Com_xx)`\n",
    "\n",
    "计数器相关：**`show global status like 'Com%';`**\n",
    "> eg：Com_select、Com_insert、Com_delete、Com_update、Com_create_index、Com_alter_table...\n",
    "\n",
    "---\n",
    "\n",
    "**`TPS = (TC2 - TC1) / 时间间隔`**\n",
    "> PS：**`TC = Com_insert + Com_delete + Com_update`**\n",
    "\n",
    "查询命令：**`show global status where variable_name in ('com_insert','com_delete','com_update','uptime');`**\n",
    "\n",
    "![2.tps.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190825190457724-135647241.png)\n",
    "\n",
    "带入公式计算就知道TPS了\n",
    "\n",
    "![2.tps_avg.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190825190915405-1752805765.png)\n",
    "\n",
    "##### 2.并发请求数\n",
    "\n",
    "这个大家都知道，**并发数越大系统性能越弱**（结合**CPU***、内存、网络IO来看）\n",
    "\n",
    "数据库**当前并发数量**：`show global status like 'Threads_running';`\n",
    "> PS：**当前连接数**：`show global status like 'Threads_connected';`\n",
    "\n",
    "![2.counts.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190825191539109-1477821129.png)\n",
    "\n",
    "**线上项目**的并发数一般**远小于**同时间数据库线程数量（如果不是，那可能出现了大量阻塞）\n",
    "> PS：**当前并发数量**（`Threads_running`） << **当前连接数**（`Threads_connected`）\n",
    "\n",
    "区分：**并发数：同时执行的会话数量**，当前**连接数：会话总数**（包括sleep的）\n",
    "\n",
    "##### 3.缓存命中率（innodb）\n",
    "\n",
    "这个主要是指innodb查询的`缓存命中率`（一般都`>=95%`）\n",
    "> 公式：`(innodb_buffer_pool_read_requests - innodb_buffer_pool_reads) / innodb_buffer_pool_read_requests`\n",
    "\n",
    "![3.demo.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190825212159030-1036611806.png)\n",
    "\n",
    "简单解释一下：\n",
    "\n",
    "1. **`innodb_buffer_pool_read_requests`：请求读取总次数**（从缓冲池中读取的次数 + 物理磁盘读取次数）\n",
    "    - PS：这个参数已经包含了`从磁盘读取次数`（两者差值就说明缓存命中的次数）\n",
    "2. **`innodb_buffer_pool_reads`：从物理磁盘读取的次数**\n",
    "\n",
    "PS：查询：`show global status like 'innodb_buffer_pool_read%';`\n",
    "\n",
    "![3.read.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190825212319179-163428797.png)\n",
    "\n",
    "---\n",
    "\n",
    "#### 4.6.6.3.DB异常的监控\n",
    "\n",
    "##### 1.检查innodb的阻塞\n",
    "\n",
    "MySQL5.7以前使用了2张表：`information_schema.innodb_lock_waits` and `information_schema.innodb_trx`\n",
    "> PS：MySQL5.7以后用一张表就行了：**`sys.innodb_lock_waits`**（本质就是上面封装的一个视图）\n",
    "\n",
    "看个案例：**摸拟两个会话同时对一条记录进行修改**\n",
    "\n",
    "![4.会话1.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190829113704512-1059272215.png)\n",
    "\n",
    "![4.会话2.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190829113708287-1180582925.png)\n",
    "\n",
    "查询阻塞时间>30s的相关信息\n",
    "> PS：可以捕获被阻塞的语句，缺没法捕获导致阻塞的语句（已经执行完了）\n",
    "\n",
    "![4.会话3.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190829113712543-274330903.png)\n",
    "\n",
    "```sql\n",
    " -- 查询阻塞时间大于30s的信息\n",
    "select waiting_pid             as blocked_pid,\n",
    "       waiting_query           as blocked_sql,\n",
    "       blocking_pid            as running_pid,\n",
    "       blocking_query          as running_sql,\n",
    "       wait_age                as blocked_time,\n",
    "       sql_kill_blocking_query as info\n",
    "from sys.innodb_lock_waits\n",
    "where (unix_timestamp() - unix_timestamp(wait_started)) > 30;\n",
    "```\n",
    "\n",
    "PS：之所以阻塞是因为**都对同一条资源占用排它锁**\n",
    "> 可以这么理解：大家都要排他锁，第一个占了lock，第二个就阻塞了\n",
    "\n",
    "---\n",
    "\n",
    "扩展：**MariaDB 5.5.60 或者小于MySQL5.7**使用这句SQL：\n",
    "\n",
    "![4.会话4.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190829122559797-1856456012.png)\n",
    "\n",
    "```sql\n",
    "select b.trx_mysql_thread_id                              as blocked_pid,\n",
    "       b.trx_query                                        as blocked_sql,\n",
    "       c.trx_mysql_thread_id                              as running_pid,\n",
    "       c.trx_query                                        as running_sql,\n",
    "       (unix_timestamp() - unix_timestamp(c.trx_started)) as blocked_time\n",
    "from information_schema.innodb_lock_waits a\n",
    "         join information_schema.innodb_trx b on a.requesting_trx_id = b.trx_id\n",
    "         join information_schema.innodb_trx c on a.blocking_trx_id = c.trx_id\n",
    "where (unix_timestamp() - unix_timestamp(c.trx_started)) > 30;\n",
    "```\n",
    "\n",
    "简单总结一下：\n",
    "\n",
    "查询当前会话的连接号：**`select connection_id();`**\n",
    "> PS：修改innodb事务锁的超时时间：`set global innodb_lock_wait_timeout=60;`（MySQL8默认是50s）\n",
    "\n",
    "MySQL命令下 `kill 线程号` 可以杀死阻塞线程\n",
    "\n",
    "**可以抓到阻塞线程的线程号，但没法准确的抓到被哪条SQL阻塞的，只能抓取正在执行中的SQL语句**\n",
    "> PS：MyISAM的表不能用这种方式检查（现在基本都是innodb，如果历史版本的Table记得改下引擎）\n",
    "\n",
    "---\n",
    "\n",
    "##### 2.检查死锁 \n",
    "\n",
    "当前事物是否产生了死锁（MySQL会主动回滚占用资源比较小的那个事物）\n",
    "> PS：如果回滚资源占用多了，那计算运行半天不就白费了？（时间和资源的浪费）\n",
    "\n",
    "微端解决：把死锁信息记录到error.log中\n",
    "> PS：因为MySQL会自动处理死锁，所以简单记录一下就行了，然后自己再场景欢迎来排除死锁\n",
    "\n",
    "`set global innodb_print_all_deadlocks=on;`（MySQL8可以使用`persist`持久化配置文件）\n",
    "> PS：查看最近一次死锁相关信息：**`show engine innodb status`**\n",
    "\n",
    "**企业级方案**：\n",
    "\n",
    "`pt-deadlock-logger u=用户名,p=密码,h=ip地址 --create-dest-table --dest u=用户名,p=密码,h=ip地址,D=数据库,t=记录死锁信息的表名`\n",
    "> PS：`--dest u=用户名,p=密码,h=ip地址,D=数据库,t=表名`~存储死锁信息到哪个数据库\n",
    "\n",
    "![6.pt.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190829141910578-1285807797.png)\n",
    "\n",
    "PS：**有一个坑点，pt连接的用户必须要有root权限，不然只能建表，但没有死锁记录，建议单独创建一个dba账号用于监控**\n",
    "\n",
    "![6.desc.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190829141923615-1357827316.png)\n",
    "\n",
    "死锁演示：\n",
    "\n",
    "![6.locks.gif](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190829141933418-1181586801.gif)\n",
    "\n",
    "死锁捕获：\n",
    "\n",
    "![6.ok.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190829141927550-1700721239.png)\n",
    "\n",
    "画个流水线的表格把，不然新手不懂：\n",
    "> 看完流水线再看gif演示就简单了（之前讲Python的死锁画了很多图，可以看看）\n",
    "\n",
    "|会话1|会话2|\n",
    "|---|---|\n",
    "|begin;|x|\n",
    "|`update workdb.users set email='dnt@188.com' where id=1;`|x|\n",
    "|x|begin;|\n",
    "|x|`update workdb.users set email='dnt@taobao.com' where id=2;`|\n",
    "|`update workdb.users set email='dnt@188.com' where id=2;`|x|\n",
    "|x|`update workdb.users set email='dnt@taobao.com' where id=1;`|\n",
    "\n",
    "---\n",
    "\n",
    "##### 3.慢查询监控\n",
    "\n",
    "两种处理方式：\n",
    "\n",
    "1. 定期对慢查询日志分析（上节讲过）\n",
    "2. 实时监控`information_schema.processlist`表\n",
    "    - eg：`select * from information_schema.processlist where time>60 and command<>'sleep';`\n",
    "\n",
    "![5.slow.png](https://img2018.cnblogs.com/blog/1127869/201908/1127869-20190829123116019-2058156332.png)\n",
    "\n",
    "PS：这个`event_scheduler`是系统的后台线程，忽略即可\n",
    "\n",
    "#### 4.6.6.4.主从相关监控\n",
    "\n",
    "##### 1.主从状态\n",
    "\n",
    "数据库主从复制链路是否正常\n",
    "\n",
    "**`show slave status;`查看`Slave_IO_Running`和`Slave_SQL_Running`**\n",
    "> PS：正常都是yes，如果是no可以在`Last_Errno`和`Last_Error`查看错误信息\n",
    "\n",
    "##### 2.主从延迟\n",
    "\n",
    "数据库主从延迟时间：\n",
    "\n",
    "微端方案：`show slave status;`查看`Seconds_Behind_Master`（主库binlog时间和从库重新执行过的binlog的时间差）\n",
    "> PS：很多开源工具都是监控这个值，但这个监控不是很准，eg：网络延迟 or 主库执行了一个耗时的事物，binlog还没同步到从库上，这时就不准了\n",
    "\n",
    "**企业级方案**：\n",
    "\n",
    "先说原理：主要就是**在主数据库中创建一个表，然后周期性的在主库中插入数据，然后读取从库中的这条数据，并统计下同步完成所耗的时间**\n",
    "\n",
    "主库：**`pt-heartbeat --user=用户名 --password=密码 -h master_ip --create-table --database 数据库名 --update --daemonize --interval=1`**\n",
    "\n",
    "从库：**`pt-heartbeat --user=用户名 --password=密码 -h slave_ip --database 数据库名 --monitor --daemonize --log /tmp/salve_tmp.log`**\n",
    "\n",
    "参考文章：\n",
    "\n",
    "```shell\n",
    "数据库架构--数据库监控\n",
    "https://blog.csdn.net/xiaochen1999/article/details/80947183\n",
    "\n",
    "mysql 查询正在执行的事务以及等待锁 常用的sql语句\n",
    "https://www.cnblogs.com/xiaoleiel/p/8316527.html\n",
    "\n",
    "使用pt-heartbeat检测主从复制延迟\n",
    "https://www.cnblogs.com/xiaoboluo768/p/5147425.html\n",
    "```\n",
    "\n",
    "##### 附录：看看innodb_lock_waits的本质\n",
    "\n",
    "验证：`show create view sys.innodb_lock_waits;`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# show create view sys.innodb_lock_waits;\n",
    "\n",
    "# -- MySQL5.7.27\n",
    "# CREATE ALGORITHM = TEMPTABLE DEFINER =`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`innodb_lock_waits` AS\n",
    "# select `r`.`trx_wait_started`                               AS `wait_started`,\n",
    "#        timediff(now(), `r`.`trx_wait_started`)              AS `wait_age`,\n",
    "#        timestampdiff(SECOND, `r`.`trx_wait_started`, now()) AS `wait_age_secs`,\n",
    "#        `rl`.`lock_table`                                    AS `locked_table`,\n",
    "#        `rl`.`lock_index`                                    AS `locked_index`,\n",
    "#        `rl`.`lock_type`                                     AS `locked_type`,\n",
    "#        `r`.`trx_id`                                         AS `waiting_trx_id`,\n",
    "#        `r`.`trx_started`                                    AS `waiting_trx_started`,\n",
    "#        timediff(now(), `r`.`trx_started`)                   AS `waiting_trx_age`,\n",
    "#        `r`.`trx_rows_locked`                                AS `waiting_trx_rows_locked`,\n",
    "#        `r`.`trx_rows_modified`                              AS `waiting_trx_rows_modified`,\n",
    "#        `r`.`trx_mysql_thread_id`                            AS `waiting_pid`,\n",
    "#        `sys`.`format_statement`(`r`.`trx_query`)            AS `waiting_query`,\n",
    "#        `rl`.`lock_id`                                       AS `waiting_lock_id`,\n",
    "#        `rl`.`lock_mode`                                     AS `waiting_lock_mode`,\n",
    "#        `b`.`trx_id`                                         AS `blocking_trx_id`,\n",
    "#        `b`.`trx_mysql_thread_id`                            AS `blocking_pid`,\n",
    "#        `sys`.`format_statement`(`b`.`trx_query`)            AS `blocking_query`,\n",
    "#        `bl`.`lock_id`                                       AS `blocking_lock_id`,\n",
    "#        `bl`.`lock_mode`                                     AS `blocking_lock_mode`,\n",
    "#        `b`.`trx_started`                                    AS `blocking_trx_started`,\n",
    "#        timediff(now(), `b`.`trx_started`)                   AS `blocking_trx_age`,\n",
    "#        `b`.`trx_rows_locked`                                AS `blocking_trx_rows_locked`,\n",
    "#        `b`.`trx_rows_modified`                              AS `blocking_trx_rows_modified`,\n",
    "#        concat('KILL QUERY ', `b`.`trx_mysql_thread_id`)     AS `sql_kill_blocking_query`,\n",
    "#        concat('KILL ', `b`.`trx_mysql_thread_id`)           AS `sql_kill_blocking_connection`\n",
    "# from ((((`information_schema`.`innodb_lock_waits` `w` join `information_schema`.`innodb_trx` `b` on ((`b`.`trx_id` = `w`.`blocking_trx_id`))) join `information_schema`.`innodb_trx` `r` on ((`r`.`trx_id` = `w`.`requesting_trx_id`))) join `information_schema`.`innodb_locks` `bl` on ((`bl`.`lock_id` = `w`.`blocking_lock_id`)))\n",
    "#          join `information_schema`.`innodb_locks` `rl` on ((`rl`.`lock_id` = `w`.`requested_lock_id`)))\n",
    "# order by `r`.`trx_wait_started`;\n",
    "\n",
    "# -- MySQL8.0.16（information_schema\n",
    "# CREATE ALGORITHM = TEMPTABLE DEFINER =`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`innodb_lock_waits`\n",
    "#     (`wait_started`, `wait_age`, `wait_age_secs`, `locked_table`, `locked_table_schema`, `locked_table_name`,\n",
    "#      `locked_table_partition`, `locked_table_subpartition`, `locked_index`, `locked_type`, `waiting_trx_id`,\n",
    "#      `waiting_trx_started`, `waiting_trx_age`, `waiting_trx_rows_locked`, `waiting_trx_rows_modified`,\n",
    "#      `waiting_pid`, `waiting_query`, `waiting_lock_id`, `waiting_lock_mode`, `blocking_trx_id`, `blocking_pid`,\n",
    "#      `blocking_query`, `blocking_lock_id`, `blocking_lock_mode`, `blocking_trx_started`, `blocking_trx_age`,\n",
    "#      `blocking_trx_rows_locked`, `blocking_trx_rows_modified`, `sql_kill_blocking_query`,\n",
    "#      `sql_kill_blocking_connection`) AS\n",
    "# select `r`.`trx_wait_started`                               AS `wait_started`,\n",
    "#        timediff(now(), `r`.`trx_wait_started`)              AS `wait_age`,\n",
    "#        timestampdiff(SECOND, `r`.`trx_wait_started`, now()) AS `wait_age_secs`,\n",
    "#        concat(`sys`.`quote_identifier`(`rl`.`OBJECT_SCHEMA`), '.',\n",
    "#               `sys`.`quote_identifier`(`rl`.`OBJECT_NAME`)) AS `locked_table`,\n",
    "#        `rl`.`OBJECT_SCHEMA`                                 AS `locked_table_schema`,\n",
    "#        `rl`.`OBJECT_NAME`                                   AS `locked_table_name`,\n",
    "#        `rl`.`PARTITION_NAME`                                AS `locked_table_partition`,\n",
    "#        `rl`.`SUBPARTITION_NAME`                             AS `locked_table_subpartition`,\n",
    "#        `rl`.`INDEX_NAME`                                    AS `locked_index`,\n",
    "#        `rl`.`LOCK_TYPE`                                     AS `locked_type`,\n",
    "#        `r`.`trx_id`                                         AS `waiting_trx_id`,\n",
    "#        `r`.`trx_started`                                    AS `waiting_trx_started`,\n",
    "#        timediff(now(), `r`.`trx_started`)                   AS `waiting_trx_age`,\n",
    "#        `r`.`trx_rows_locked`                                AS `waiting_trx_rows_locked`,\n",
    "#        `r`.`trx_rows_modified`                              AS `waiting_trx_rows_modified`,\n",
    "#        `r`.`trx_mysql_thread_id`                            AS `waiting_pid`,\n",
    "#        `sys`.`format_statement`(`r`.`trx_query`)            AS `waiting_query`,\n",
    "#        `rl`.`ENGINE_LOCK_ID`                                AS `waiting_lock_id`,\n",
    "#        `rl`.`LOCK_MODE`                                     AS `waiting_lock_mode`,\n",
    "#        `b`.`trx_id`                                         AS `blocking_trx_id`,\n",
    "#        `b`.`trx_mysql_thread_id`                            AS `blocking_pid`,\n",
    "#        `sys`.`format_statement`(`b`.`trx_query`)            AS `blocking_query`,\n",
    "#        `bl`.`ENGINE_LOCK_ID`                                AS `blocking_lock_id`,\n",
    "#        `bl`.`LOCK_MODE`                                     AS `blocking_lock_mode`,\n",
    "#        `b`.`trx_started`                                    AS `blocking_trx_started`,\n",
    "#        timediff(now(), `b`.`trx_started`)                   AS `blocking_trx_age`,\n",
    "#        `b`.`trx_rows_locked`                                AS `blocking_trx_rows_locked`,\n",
    "#        `b`.`trx_rows_modified`                              AS `blocking_trx_rows_modified`,\n",
    "#        concat('KILL QUERY ', `b`.`trx_mysql_thread_id`)     AS `sql_kill_blocking_query`,\n",
    "#        concat('KILL ', `b`.`trx_mysql_thread_id`)           AS `sql_kill_blocking_connection`\n",
    "# from ((((`performance_schema`.`data_lock_waits` `w` join `information_schema`.`INNODB_TRX` `b` on ((\n",
    "#         convert(`b`.`trx_id` using utf8mb4) =\n",
    "#         cast(`w`.`BLOCKING_ENGINE_TRANSACTION_ID` as char charset utf8mb4)))) join `information_schema`.`INNODB_TRX` `r` on ((\n",
    "#         convert(`r`.`trx_id` using utf8mb4) =\n",
    "#         cast(`w`.`REQUESTING_ENGINE_TRANSACTION_ID` as char charset utf8mb4)))) join `performance_schema`.`data_locks` `bl` on ((`bl`.`ENGINE_LOCK_ID` = `w`.`BLOCKING_ENGINE_LOCK_ID`)))\n",
    "#          join `performance_schema`.`data_locks` `rl` on ((`rl`.`ENGINE_LOCK_ID` = `w`.`REQUESTING_ENGINE_LOCK_ID`)))\n",
    "# order by `r`.`trx_wait_started`;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "---\n",
    "\n",
    "### 1.6.7.MySQL备份恢复\n",
    "\n",
    "SQLServer的备份在2017年的时候就说过，图形+SQL+Demo，感兴趣的同志可以回顾一下：\n",
    "> <https://www.cnblogs.com/dunitian/p/6260481.html>\n",
    "\n",
    "说到数据库备份，常想到的就是这三大块：**全备份**、**增量备份**、**差异备份**，这个在SQLServer中是最常见的备份方式\n",
    "\n",
    "但说到MySQL咱们往往最常使用的是**物理备份**（物理文件拷贝），然后才是**逻辑备份**（生成sql文件）\n",
    "> PS：逻辑备份的可移植性比较强，但恢复效率较低；物理备份移植性比较差（DB的环境版本要一致）但恢复效率较高\n",
    "\n",
    "提示：如果是诸如Memory类型的表（数据没存在磁盘中）那么物理备份只能备份表结构\n",
    "> PS：不管是逻辑备份还是物理备份，通常都需要使用binlog ==> 也**对binlog进行**一波**备份**\n",
    "\n",
    "#### 1.6.7.1.全备、差异备份、增量备份的概念\n",
    "\n",
    "画一张图说明一下差异备份：**在上一次全备的基础上进行差异备份** ==> 每次备份文件都会越来越大，直到下一次全备份\n",
    "> PS：第一天全备了，那么第2天差异备份就只会备份第2天本身新增的数据，第3天则会备份第2天和第3天新增的数据\n",
    "\n",
    "![1.差异.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191211162710504-1264075800.png)\n",
    "\n",
    "**增量**备份则**只备份每天新增的数据**（PS：SQLServer中我们最常用的就是全备份和增量备份）\n",
    "> PS：缺点也很明显，还原数据的时候需要先还原最近的全备份，然后依次还原每次的增量备份，恢复所需要的时间就比较长了\n",
    "\n",
    "![2.增量.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191211163354145-1242188705.png)\n",
    "\n",
    "#### 1.6.7.2.常用工具\n",
    "\n",
    "| 名称       | 特点                                            |\n",
    "| ---------- | ----------------------------------------------- |\n",
    "| mysqldump  | mysql自带工具，支持全量备份和条件备份（单线程） |\n",
    "| mysqlpump  | 多线程逻辑备份工具，mysqldump的增强版           |\n",
    "| xtrabackup | innodb在线物理备份工具，支持多线程和增量备份    |\n",
    "\n",
    "备份不一定是root账号，只要具有 **`select、reload、lock tables、replication client、show view、process`** 权限就可以了（如果使用`--tab`那么还需要`file`权限）\n",
    "> PS：`create user 'dbbak'@'%' identified by '密码';`  `grant select,reload,lock tables,replication client,show view,process on *.* to 'dbbak'@'%';`\n",
    "\n",
    "##### 1.mysqldump\n",
    "\n",
    "mysqldump在对表备份的时候会加锁，这时候线上有写操作产生就会阻塞（表越大阻塞时间越长）\n",
    "> PS：原理就是**先读出来**，然后写入备份sql文件中，这个过程会污染innodb buffer pool，可能就会把热数据挤出，那查询性能就必然会下降\n",
    "\n",
    "先看下帮助文档（最常用：**`mysqldump [OPTIONS] database [tables]`**）\n",
    "\n",
    "![3.mysqldump帮助.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191211212451337-1969678044.png)\n",
    "\n",
    "```shell\n",
    "[OPTIONS]\n",
    "\n",
    "# 数据库中有这些就加上\n",
    "-E, --events             # 备份计划任务\n",
    "-R, --routines           # 备份存储过程和函数\n",
    "--triggers               # 备份触发器（一般用不到）\n",
    "\n",
    "-F, --flush-logs         # 备份的时候同时刷新一份新的二进制日志\n",
    "--master-data=1|2        # 将二进制的信息（名称、偏移量）写入到sql中（1生成CHANGE MASTER，2则注释掉）\n",
    "--max-allowed-packet=x   # 设置最大包的值（<=目标库的max-allowed-packet值）\n",
    "\n",
    "--single-transaction     # 用于备份事务性存储引擎时使用（保证数据一致性）\n",
    "-l, --lock-tables        # 锁定指定数据库下的所有表（备份过程中只能读）\n",
    "-x, --lock-all-tables    # 锁定所有数据库的表\n",
    "\n",
    "-w, --where=name         # 条件备份时候使用（只支持单表的导出）\n",
    "\n",
    "--hex-blob               # 以16进制的形式保存（防止出现文本乱码）\n",
    "--tab=path               # 为数据库中的每个表生成两个文件，一个存储表结构，一个存储表数据（一般都是mysqldump与mysqld在同一台机器才使用）\n",
    "```\n",
    "\n",
    "再说下`--single-transaction`，这个参数一般在存储引擎都是InnoDB的时候使用，备份的时候会启动一个事物来保证数据的一致性\n",
    "> PS：如果有其他非事务性存储引擎，那么只能使用低效率的`--lock-tables`了（这也是为什么推荐存储引擎全部使用InnoDB）\n",
    "\n",
    "###### 备份\n",
    "\n",
    "这边就简单演示下**备份数据库**：`mysqldump -uroot -p --databases safe_db > safe_db.sql`\n",
    "\n",
    "![4.mysqldump_demo1.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191212080720755-918979236.png)\n",
    "\n",
    "PS：完整写法：**`mysqldump -uroot -p --master-data=2 --single-transaction --events --routines --triggers db_name > db_name.sql`**\n",
    "\n",
    "![4.完整写法.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191213130733186-245751693.png)\n",
    "\n",
    "---\n",
    "\n",
    "有时候我们还会对数据库中需要分析的**表进行备份**，然后在新环境下进行进一步的分析（比如访问ip、上传记录等）\n",
    "> eg：`mysqldump -uroot -p safe_db users> safe_db_users_tb.sql`\n",
    "\n",
    "![5.mysqldump_备份指定表.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191212081308859-1287164274.png)\n",
    "\n",
    "---\n",
    "\n",
    "在主从模式下还会使用到**`master-data`**，我们也来简单演示下，这个到底是个啥（看图秒懂）\n",
    "> eg：`mysqldump -uroot -p --database safe_db --master-data=2 > safe_db2.sql`\n",
    "\n",
    "![6.masterdata测试.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191212083544203-437120798.png)\n",
    "\n",
    "下面见证奇迹的时刻到了~区别只是有没有把**binlog文件名和偏移量**注释而已\n",
    "\n",
    "![6.1.对比图.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191212083915613-695261809.png)\n",
    "\n",
    "然后说过**踩的坑**：使用`--master-data`的时候如果没有使用`--single-transaction`，那么DB默认会使用`--lock-all-tables`\n",
    "> PS：这也是为什么各大公司都推荐，建表的时候都使用InnoDB的根本原因 ==> 不影响线上的后期维护太麻烦\n",
    "\n",
    "---\n",
    "\n",
    "再说个场景：数据库数据都是假删，有些时候这些删除的数据比真实数据还要多，在一些场景下需要收缩数据库，那么就可以使用**条件备份**\n",
    "> eg：`mysqldump -uroot -p safe_db file_records --where \"datastatus=1\" > safe_db_file_records_tb.sql`\n",
    "\n",
    "先看下表结构\n",
    "\n",
    "![6.2.先看下表结构.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191212124316662-833256021.png)\n",
    "\n",
    "导出和恢复对比图（新数据库中表数据只含符合条件的）\n",
    "> PS：`mysql -uroot -p safe_db_bak < safe_db_file_records_tb.sql`（只支持单表的导出）\n",
    "\n",
    "![6.3.导出和恢复.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191212125750627-283508495.png)\n",
    "\n",
    "PS：旧表删不删除你得看SQL ==> `DROP TABLE IF EXISTS file_records;`\n",
    "\n",
    "---\n",
    "\n",
    "最后再提一个常用的场景：**结构和数据分开备份**（`--tab=path`）\n",
    "> eg：`mysqldump -uroot -p --master-data=2 --single-transaction --events --routines --triggers --tab=/tmp safe_db`\n",
    "\n",
    "使用`--tab`mysql备份账户必须有file的权限才能操作，然后指定的目录mysql必须有可写权限\n",
    "> PS：没权限 ==> `chown mysql:mysql 目录名` or `/tmp`\n",
    "\n",
    "![5.1.结构和数据分开存储.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191213130131581-580167129.png)\n",
    "\n",
    "因为安全性的缘故，CentOS中Mysql写入tmp的文件都在这个临时文件夹中，如果你删除了这个临时文件夹，那么只会写入失败（重启mysql方能生效）\n",
    "> PS：这个我在讲DB系统表的时候说过（`select into outfile` and `secure_file_priv`），可以一览：<https://mp.weixin.qq.com/s/rQnMlllRSlyseEmSOCTVGw>\n",
    "\n",
    "文字很抽象，一张图示即可秒懂\n",
    "> PS：根本原因就是因为`select into outfile`会导致一些安全问题，CentOS做了处理，而MySQL还是以为自己直接写入/tmp中了（后面说恢复会讲）\n",
    "\n",
    "![5.2.为什么.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191213130141936-1417911351.png)\n",
    "\n",
    "---\n",
    "\n",
    "###### 恢复\n",
    "\n",
    "恢复就比较简单了，但是单线程的操作，数据库内容较多可能就得等会了（这也是为什么线上故障恢复常用物理备份的原因），简单提一下：`mysql -uroot -p safe_db_bak < safe_db2.sql`\n",
    "> PS：在mysql命令行下使用`source /xxx/safe_db2.sql`也一样\n",
    "\n",
    "如果没创建新DB就会报错\n",
    "> 快速创建：`mysql -uroot -p -e \"create database safe_db_bak charset=utf8\"`\n",
    "\n",
    "![7.恢复1.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191212104405527-2119542605.png)\n",
    "\n",
    "![7.恢复2.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191212104413648-63829153.png)\n",
    "\n",
    "简单提几个可能出现的错误：\n",
    "\n",
    "其一：`mysqldump: Error: Binlogging on server not active` ==> binlog没有打开，如果不会可以看看之前写的binlog文章\n",
    "> 博客园：<https://www.cnblogs.com/dotnetcrazy/p/11029323.html>，微信：<https://mp.weixin.qq.com/s/58auoCY0SU7qNjdTa5dc_Q>\n",
    "\n",
    "其二：`@@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty`，这个在集群的情况下会遇到，简单说下解决：\n",
    "> 1.mysql命令行中输入`reset mater`；2.执行mysqldump的时候后面加个参数`--set-gtid-purged=off`(不导出gtid的信息）\n",
    "\n",
    "最后再提下上面说的分结构和数据的导入方式：`source /tmp/xxx.sql;` and `load data infile '/tmp/xxx.txt' into table tb_name;`\n",
    "\n",
    "恢复表结构：`source /tmp/file_records.sql;`\n",
    "\n",
    "![7.1.恢复表结构.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191213140500877-1542113045.png)\n",
    "\n",
    "恢复表数据：`load data infile '/tmp/file_records.txt' into table file_records;`\n",
    "\n",
    "![7.1.恢复表数据.png](https://img2018.cnblogs.com/blog/1127869/201912/1127869-20191213140516944-827947557.png)\n",
    "\n",
    "若出现任何异常（权限也好，错误也罢）依旧可看我上面贴的文章，里面讲这些已经很透彻了\n",
    "> PS：<https://mp.weixin.qq.com/s/rQnMlllRSlyseEmSOCTVGw>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "##### 2.mysqlpump\n",
    "\n",
    "`mysqlpump`是5.7之后自带的工具，不需要自行安装而且**语法和mysqldump基本相同**（可以理解为多线程版**mysqldump**）\n",
    "> PS：新增了zlib和lz4压缩方式（以前需要自己压缩）\n",
    "\n",
    "再来说下不足点：\n",
    "\n",
    "1. 虽说是并行备份，但大表来说性能较差\n",
    "2. 5.7.11之前并发备份和一致性备份不能同时满足（不能同时使用并行备份和一致性两个参数）\n",
    "3. 同样是会污染InnoDB Buffer Pool"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MySQL高级\n",
    "\n",
    "\n",
    "**高可用注意下：MariaDB的Gtid复制和MySQL不兼容，多主复制也和MySQL不一样**\n",
    ">  GTID即全局事务ID，GTID实际上是由`UUID`+`TID`组成的。其中UUID是一个`MySQL实例的唯一标识`。TID代表了该实例上`已经提交的事务数量`，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）eg：`4e659069-3cd8-11e5-9a49-001c4270714e:1-77`\n",
    "\n",
    "\n",
    "\n",
    "日志审计（监测）用户活动\n",
    "\n",
    "---\n",
    "\n",
    "数据库升级前需要考虑：\n",
    "eg：MySQL之前修改Json是全部复制，MySQL8.0对Json数据的复制只复制修改的部分\n",
    "\n",
    "---\n",
    "\n",
    "innodb专题：\n",
    "\n",
    "for update：请求写锁（排它锁，独占，别人没法读写了）\n",
    "lock in share mode：读锁（共享锁）\n",
    "\n",
    "- Redo Log：实现事物的持久性\n",
    "    - 内存中的缓冲区、重构日志文件\n",
    "    - PS：顺序写入\n",
    "- Undo Log：未提交事物的回滚\n",
    "    - 随机读写\n",
    "\n",
    "锁的作用：\n",
    "- 实现事物的隔离性\n",
    "- 管理共享资源的并发访问\n",
    "\n",
    "锁的粒度：\n",
    "- 表级锁（并发性低）\n",
    "    - 独占锁：`lock table 表名 write;` \n",
    "    - 解锁：`unlock tables;`\n",
    "- 行级锁（并发性高）\n",
    "\n",
    "innodb锁的类型：\n",
    "- 共享锁（读锁）\n",
    "- 独占锁（写锁）\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.7.数据交互\n",
    "\n",
    "### 1.PyMySQL\n",
    "\n",
    "官方文档：<https://github.com/PyMySQL/PyMySQL>\n",
    "\n",
    "### 2.aiomysql\n",
    "\n",
    "官方文档：<https://github.com/aio-libs/aiomysql>\n",
    "\n",
    "### 类比：NodeJS操作MySQL\n",
    "\n",
    "官方文档：<https://github.com/mysqljs/mysql>\n",
    "\n",
    "### 3.ORM\n",
    "\n",
    "基础回顾：<https://www.cnblogs.com/dotnetcrazy/p/9333792.html#3.3.元类系列>\n",
    "\n",
    "#### 3.1.元类基础\n",
    "\n",
    "知道最基本的一点就行了：**通过元类(`type`)创建出类对象(`class`)，通过类对象创建出示例对象**\n",
    "> PS：创建类的时候如果没指定`metaclass`就使用`Type`创建，如果指定了就以指定的`方法|类`来创建\n",
    "\n",
    "看个验证案例："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Student\n",
      "(<class '__main__.People'>, <class '__main__.China'>)\n",
      "{'__module__': '__main__', '__qualname__': 'Student', '__init__': <function Student.__init__ at 0x00000296A9B32B70>, 'show': <function Student.show at 0x00000296A9B32510>}\n",
      "小明 25 中科院\n"
     ]
    }
   ],
   "source": [
    "# 方法\n",
    "def meta_fun(class_name, parent_class_tuple, class_attr_dict):\n",
    "    print(class_name, parent_class_tuple, class_attr_dict, sep=\"\\n\")\n",
    "    # 这个就是默认做的事情，我这边只是演示一下，指定了metaclass就会使用自定义的方法或者类\n",
    "    return type(class_name, parent_class_tuple, class_attr_dict)\n",
    "\n",
    "class People(object):\n",
    "    location = \"地球\"\n",
    "\n",
    "class China(object):\n",
    "    skin = \"黄皮肤\"\n",
    "\n",
    "class Student(People, China, metaclass=meta_fun):\n",
    "    def __init__(self, name, age, school):\n",
    "        self.name = name\n",
    "        self.age = age\n",
    "        self.school = school\n",
    "\n",
    "    def show(self):\n",
    "        print(self.name, self.age, self.school)\n",
    "\n",
    "def main():\n",
    "    xiaoming = Student(\"小明\", 25, \"中科院\")\n",
    "    xiaoming.show()\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Student\n",
      "(<class '__main__.People'>, <class '__main__.China'>)\n",
      "{'__module__': '__main__', '__qualname__': 'Student', '__init__': <function Student.__init__ at 0x00000296A9B329D8>, 'show': <function Student.show at 0x00000296A9B32598>}\n",
      "小明 25 中科院\n"
     ]
    }
   ],
   "source": [
    "# 类\n",
    "class MetaClass(type):\n",
    "    # 在__init__之前执行（__new__：创建对象并返回【单例模式会用到】）\n",
    "    def __new__(cls, class_name, parent_class_tuple, class_attr_dict):\n",
    "        print(class_name, parent_class_tuple, class_attr_dict, sep=\"\\n\")\n",
    "\n",
    "        return type(class_name, parent_class_tuple, class_attr_dict)\n",
    "        # 也可以这么做：type.__new__(cls,x,x,x)\n",
    "        # return super().__new__(cls, class_name, parent_class_tuple, class_attr_dict)\n",
    "\n",
    "class People(object):\n",
    "    location = \"地球\"\n",
    "\n",
    "class China(object):\n",
    "    skin = \"黄皮肤\"\n",
    "\n",
    "class Student(People, China, metaclass=MetaClass):\n",
    "    def __init__(self, name, age, school):\n",
    "        self.name = name\n",
    "        self.age = age\n",
    "        self.school = school\n",
    "\n",
    "    def show(self):\n",
    "        print(self.name, self.age, self.school)\n",
    "\n",
    "def main():\n",
    "    xiaoming = Student(\"小明\", 25, \"中科院\")\n",
    "    xiaoming.show()\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "简单提一下：`__new__`+`__init__`相当于C#里面的构造方法\n",
    "> **PS：`__new__`和`__init__`的方法参数是一样的**\n",
    "\n",
    "#### 3.2.手写ORM\n",
    "\n",
    "\n",
    "\n",
    "---\n",
    "\n",
    "### 4.SQLAlchemy\n",
    "\n",
    "最新地址：<https://github.com/sqlalchemy/sqlalchemy>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "知识点：\n",
    "```shell\n",
    "当天的时间：date +%Y%m%d%H%M ==> 201902211407\n",
    "一周前时间：date -d '-7 day' +%Y%m%d%H%M ==> 201902141407\n",
    "三分钟前：date -d '-3 minutes' +%Y%m%d%H%M ==> 201902211404\n",
    "\n",
    "压缩文件：tar -zcvf 时间.sql.tar.gz 数据库.sql\n",
    "\n",
    "数据库备份：mysqldump -uroot -p密码 -B 数据库 > /bak/数据库.sql\n",
    "\n",
    "定时任务：\n",
    "```\n",
    "\n",
    "## 1.8.高级\n",
    "\n",
    "最后不要在主库上进行数据库备份（会影响磁盘性能）大促销活动前取消这类计划任务\n",
    "\n",
    "架构相关（分库分表）、监控优化\n",
    "\n",
    "## 1.9.扩展\n",
    "\n",
    "\n",
    "### NoSQL列式存储\n",
    "```\n",
    "百科：https://baike.baidu.com/item/列式数据库\n",
    "\n",
    "行数据库\n",
    "EmpId,Lastname,Firstname,Salary\n",
    "1,Smith,Joe,40000; 2,Jones,Mary,50000; 3,Johnson,Cathy,44000;\n",
    "\n",
    "列数据库\n",
    "EmpIds,Lastnames,Firstnames,Salarys\n",
    "1,2,3; Smith,Jones,Johnson; Joe,Mary,Cathy; 40000,50000,44000;\n",
    "```"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
